472,127 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

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

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 Sub

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.
The result that I want to get is like below:
Where and how to sort the date by day? Can someone guide me thru? many thanks
Sep 21 '07 #1
3 2405
2,653 Expert 2GB
Hi, there.

Take a look at VBA function Day().
Sep 21 '07 #2
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
2,653 Expert 2GB
You are welcome.

Best regards,
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.