Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    please can u MDB put a sample fot it .
    ashraf

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Crediton, Devon, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

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

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank u MDB i do it . thanks again
    ashraf

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  15. #15
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •