469,643 Members | 2,036 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

How to sort date regardless of month and year in query or report for Access 2000?

48
I need to sort the "DateOfBirth" by the day (dd) regarless of month (mm)and year (yyyy). I have a query called q_DC_Client, in criteria, I am using this code below:

Like "*" & "/" & [Forms]![F_DC_Birthday]![ComboBdayMonth] & "/" & "*"

I have a form called F_DC_Birthday with combo box called ComboBdayMonth. The combo takes information from T_Month table, where I only have two field, first field is the name of Month (eg, January, February...etc) and second field is 01 to 12. The code I use in form is as below:

[HTML]Private Sub btn_RptBirthdayDC_Click()
On Error GoTo Err_btn_RptBirthdayDC_Click

Dim stDocName As String

stDocName = "Rpt_Birthday_DC"
DoCmd.OpenReport stDocName, acPreview

Exit_btn_RptBirthdayDC_Click:
Exit Sub

Err_btn_RptBirthdayDC_Click:
MsgBox Err.Description
Resume Exit_btn_RptBirthdayDC_Click


End Sub[/HTML]

I can get report of clients who have birthday on particular month that I choose from the form, however, I need the report to sort clients according to the day. In report called Rpt_Birthday_DC, I had set Sorting and Grouping of the DateOfBirth to Ascending. It sort by the year follow by the month and then day.
20/10/1920
15/10/1932
28/10/1945
01/10/1950
The result that I want to get is like below:
01/10/1950
15/10/1932
20/10/1920
28/10/1945
Where and how to sort the date by day? Can someone guide me thru? many thanks
Sep 21 '07 #1
3 2300
FishVal
2,653 Expert 2GB
Hi, there.

Take a look at VBA function Day().
Sep 21 '07 #2
HowHow
48
Hi, there.

Take a look at VBA function Day().

Thank you! it works!
In query, I enter the code below in the field, set sort to ascending, and untick "show"

Expand|Select|Wrap|Line Numbers
  1. Expr 1: Day([DateOfBirth]) 
Sep 25 '07 #3
FishVal
2,653 Expert 2GB
You are welcome.

Best regards,
Fish
Sep 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Lapchien | last post: by
2 posts views Thread by B Briant | last post: by
13 posts views Thread by Alan | last post: by
2 posts views Thread by LadySugar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.