Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sorting by year, quarter, month, week, day, etc.
How To
Many people don't realize it, but Access provides a built-in method for sorting date fields by a number of different levels, such as by year, quarter, etc.
You can set all of this up by adding the date field to the Sorting and Grouping window, then clicking "Group On". The options available to you are dependent on the datatype. So, a date field will give you a number of unique grouping options, including "Year", "Qtr", "Month", "Week", "Day", "Hour", and "Minute".
Along with these date groupings, there is a function that will help you determine which day of the week a certain date is. Here is some code that will highlight the "WeekDay" function:
Dim dateTest As Date
dateTest = #3/31/2003#
MsgBox Weekday(dateTest)
'Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7
You will see that it returns the value of "2".
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
You can set all of this up by adding the date field to the Sorting and Grouping window, then clicking "Group On". The options available to you are dependent on the datatype. So, a date field will give you a number of unique grouping options, including "Year", "Qtr", "Month", "Week", "Day", "Hour", and "Minute".
Along with these date groupings, there is a function that will help you determine which day of the week a certain date is. Here is some code that will highlight the "WeekDay" function:
Dim dateTest As Date
dateTest = #3/31/2003#
MsgBox Weekday(dateTest)
'Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7
You will see that it returns the value of "2".
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org