SQL : ORDER BY Month() (Access ’97 SR1)

Topic

New Reply

I run a SELECT which shows which employees have birthdays coming in this and the next calendar month. The SQL used at present ends in

…… WHERE Month(DateOfBirth) = Month(Date()) OR Month(DateOfBirth) = (Month(Date()) + 1) MOD 12 ORDER BY Month(DateOfBirth), Day(DateofBirth), Surname

which gives me a sensible table with June’s birthdays before July’s, etc. except at this time of year – when the month numbers roll over MOD 12. So in the present list, the January birthdays (Month number 1) display before the December ones (Month number 12). Does anyone have a cunning bit of code please to improve the ORDER clause so that the months appear in the logical way throughout the year?

Thanks. – Mike –

Reply | Quote

Replies

The initial inclination is to sort it by the DateofBirth field, but I presume that you are storing the year of birth as well, so that creates complications. It seems you probably want to do a calculation on the selected birthdays to determine the next birthday by appending a year that is the current year, or the current year plus one if the month is January. The DateSerial function should do the trick so that you are basically calculating the actual date of the person’s next birthday. Seems like kind of a kludge, but I can’t think of any more elegant approach.

Reply | Quote

Here’s one way you can do this, using a calculated field which equates to True or False as first item in ORDER BY clause. Note: Your selection criteria will select only NOV birthdays if current month is NOV because 11 + 1 Mod 12 = 0! So I modified selection criteria with additional OR criteria w/o the MOD 12. Note I used DatePart function with “m” as interval which is equivalent of Month function. Sample SQL:

PARAMETERS [Enter Date:] DateTime;
SELECT DatePart(“m”,[DOB]) AS MON, DatePart(“d”,[DOB]) AS [DAY], UCase(Format([DOB],”mmm d”)) AS BIRTHDAY, LNAME, FNAME, DOB
FROM PERSONNEL
WHERE (((DatePart(“m”,[DOB]))=DatePart(“m”,[Enter Date:]) Or (DatePart(“m”,[DOB]))=(DatePart(“m”,[Enter Date:])+1) Mod 12) AND ((DOB) Is Not Null)) OR (((DatePart(“m”,[DOB]))=DatePart(“m”,[Enter Date:]) Or (DatePart(“m”,[DOB]))=(DatePart(“m”,[Enter Date:])+1)) AND ((DOB) Is Not Null))
ORDER BY (DatePart(“m”,[DOB])=12) And DatePart(“m”,[Enter Date:])=12, DatePart(“m”,[DOB]), DatePart(“d”,[DOB]), LNAME, FNAME;

Note: I replaced Date() function with a parameter ([Enter Date:] to be able to test this with different months as “current” month. In actual query you can replace parameter with Date() function. In example DOB is Date of Birth date/time field, PERSONNEL is name of table.

HTH

Reply | Quote

Try this one:

…… WHERE Month(DateOfBirth) = Month(Date()) OR Month(DateOfBirth) =Month(dateadd(“m”,1,Date())) ORDER BY Month(DateOfBirth), Day(DateofBirth), Surname

Reply | Quote

I’m very grateful to all three of you for your suggestions. In the end I went MarkD’s way because not only did it work, but it was a type of solution which I would never have thought of exploring myself. All I’ve got to do now is be sure I really understand how it works (*grin*).

Again, my thanks to you. – Mike –

Reply | Quote

please can u MDB put a sample fot it .
ashraf

Reply | Quote

You can use MarkD’s code directly (see Post 203094 higher up this thread). You just need to make up a table called PERSONNEL, with a datefield called DOB for the person’s date of birth, two text fields LNAME and FNAME for last name and first name respectively.

When you run MarkD’s SQL you get everybody whose birthday is in this month or next month, ordered in the same way.

HTH.

Reply | Quote

Sorry to come in so late, but when you guys were talking I was fast asleep, must be my age.
Another SQL to do this would be:

SELECT Personnel.DOB, Personnel.LName, Personnel.FName
FROM Personnel
WHERE (((Personnel.DOB)>DateSerial(Year([DE]),Month([de]),0) And (Personnel.DOB)<=DateSerial(Year([DE]),Month([de])+2,0)))
ORDER BY Personnel.DOB;

From Wendell's post I gather he was suggesting something along these lines.

Pat

Reply | Quote

Yep, the problem is that SQL will sort by Date Of Birth, which means that someone born in January 1980 will sort before someone born in December of 1981, which is not the sort sequence desired.

Reply | Quote

You are quite right, I was confused by the question.
Pat

Reply | Quote

Thank u MDB i do it . thanks again
ashraf

Reply | Quote

Glad it worked OK. The SQL may look sort of convoluted, but way it works is simple: you can sort on a calculated field that equates to true or false. In VB/VBA, True = -1 while False = 0, thus records will be sorted by Expression = True, then Expression = False (unless you specify descending order for calculated field). In this example, the expression:

(DatePart(“m”,[DOB])=12) And DatePart(“m”,Date())=12

will equate to True only if both DOB month AND current month are December (12). Thus DEC birthdays will sort before JAN birthdays because the expression will equate to False if DOB is in JAN. For any current month other than DEC, the expression will equate to False, so records will be sorted in “normal” sequence based on month and day component of DOB field, then by name. I modified SQL from a “Birthday Report” query I use in one program, only it is a bit simpler as the report simply lists all birthdays from JAN thru DEC.

Using the IIF or Switch functions in a calculated query field can also be useful in creating a “customized” sort order.

HTH

Reply | Quote

That’s a neat solution Mark. But on thinking more about this, a problem exists if MS ever change the value of True and False, in which case your sort order could have problems.

As you suggested one could use the iif or switch functions to accomplish.
I have worked out a less convoluted (as you put it) query and I think a bit more understandable (my opinion of course).

Here goes:

SELECT iif(Month(Personnel.DOB)=1 and Month([DE])=12,13,Month(DOB)) as Mth, DOB, Personnel.LName, Personnel.FName
FROM Personnel
WHERE Month(DOB)=Month([DE]) or Month(DOB) = (Month([DE])+1) mod 12
ORDER BY iif(Month(Personnel.DOB)=1 and Month([DE])=12,13,Month(DOB)), Day(DOB)

This query substitutes 13 for 1 if the Date Entered has a month of 12 and the DOB has a month of 1.

HTH
Pat

Reply | Quote

Actually, SQL Server uses True = 1 and False = 0, which would sort in the reverse order to Access.

Reply | Quote

It could even be done without the help of iif-stmts by using an expressing like this in the ORDER clause: Abs(Month(Date())-Month(BirthDate))

The expression will evaluate like this:

Current month	Birthday this month	Birthday next month
12	0	11
1 thru 11	0	1

One way of writing the complete SQL could be like this (using the Employees table of the Northwind sample dbs):

SELECT Employees.BirthDate, Employees.LastName
FROM Employees
WHERE Month(BirthDate)=Month([DE]) Or Month(BirthDate)=Month(DateAdd(“m”,1,[DE]))
ORDER BY Abs(Month([DE])-Month(BirthDate)), Day(BirthDate);

Replace [DE] with Date() to make it work for real.

Reply | Quote

The SQL you posted is less convoluted. But when I tested it with NOV as current month query returned birthdays in NOV only. As noted previously, that’s because 11 + 1 Mod 12 = 0. I would modify WHERE criteria to:

WHERE (((Month([DOB]))=Month([DATE:]))) OR (((Month([DOB]))=(Month([DATE:])+1) Or (Month([DOB]))=(Month([DATE:])+1) Mod 12))

You make a good point that you should not rely on the numerical value of True or False. In other programming languages such a C or C++ True has a numerical value of 1, not -1. In fact, in the Beta 1 release of VB.NET (the next generation of VB), Microsoft attempted to change True to 1 so that VB would be consistent with the other .NET languages like C#. But the VB developer community raised a ruckus, so in Beta 2 MS backtracked and reverted to True = -1. In his book Moving to VB.NET: Strategies, Concepts, and Code Dan Appleman (the WIN32 API guru) writes:
[indent]

---

Microsoft made a valiant effort in Beta 1 to bring some sanity to Visual Basic’s traditional handling of Boolean variables by attempting to redefine the And and Or operators to always represent logical Boolean operators. …. Unfortunately, a number of shortsighted VB6 programmers managed to convince Microsoft that this change somehow violated the principals that made Visual Basic great and Microsoft, for some equally incomprehensible reason, caved. As a result, VB.NET continues to define True as -1 and to combine logical and Boolean operators in the same command.

---

[/indent]
This refers to fact that in VB logical operators such as AND and OR also act as bitwise operators. This can result in unexpected results if comparing numerical values. (A bitwise operator compares the bit positions in both expressions and sets or clears the corresponding bit in the result, depending on the operator used.) Example from Immediate window:

? CBool(4)
True
? CBool(8)
True
? CBool(4 And 8)
False

You might expect True And True to equate to True, not False. A bitwise comparison with AND operator returns 1 if both bits are 1, and 0 in any other case. Since 4 = 0100 in binary and 8 = 1000, the result is 0000, or False, as shown above. While a bit off the original subject, it is another good reason that, as Pat notes, in VB/VBA you should not reply on the numerical value of True and False or use numerical values for Boolean comparisons. (And do not ever use the result of calculations involving floating-point numbers as the basis for a logical comparision!) On the other hand, since MS wound up NOT redefining True as 1 in VB.NET (the most sweeping overhaul of the language since VB 4.0, which was rewritten from gound up to support the 32-bit Windows OS) it appears likely that in VB/VBA True and False will retain their current numerical values of -1 and 0 respectively for the immediate future.

Reply | Quote

Well Mark, you are quite right, in my attempts to get Dec/Jan (the exception) to work I had overlooked the normal (is there such a thing?). Good pickup.
I’m not too sure that because a few got their way that I would stick with the values that True and False provide. Who knows MS may go with the majority one day (Ha ha !!).
As Claus has shown, his is the less convoluted so far.
The prize goes to Claus.
Pat

Reply | Quote

It must’ve been that snooze you were taking…. I agree, Claus gets 1st prize for Least Convoluted Solution.

Reply | Quote