By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,002 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,002 IT Pros & Developers. It's quick & easy.

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

P: 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
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Take a look at VBA function Day().
Sep 21 '07 #2

P: 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
Expert 2.5K+
P: 2,653
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.