# Thread: SQL : ORDER BY Month() (Access '97 SR1)

1. ## SQL : ORDER BY Month() (Access '97 SR1)

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 -

2. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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.

3. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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

4. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

Try this one:

...... WHERE Month(DateOfBirth) = Month(Date()) OR Month(DateOfBirth) =Month(dateadd("m",1,Date())) ORDER BY Month(DateOfBirth), Day(DateofBirth), Surname

5. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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 -

6. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

please can u MDB put a sample fot it .
ashraf

7. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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.

8. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

Sorry to come in so late, but when you guys were talking I was fast asleep, must be my age. <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>
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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

9. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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.

10. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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

11. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

Thank u MDB i do it . thanks again
ashraf

12. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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

13. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

14. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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

15. ## Re: SQL : ORDER BY Month() (Access '97 SR1)

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:

<table border=1><td>Current month</td><td>Birthday this month</td><td>Birthday next month</td><td>12</td><td>0</td><td>11</td><td>1 thru 11</td><td>0</td><td>1</td></table>

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