Access
Displaying specific dates
- Details
- Category: Date and Time Date and Time
- Last Updated: 31 August 2011 31 August 2011
Displaying specific dates
To display specific dates, you can use the DateSerial() function to manipulate the day, month, and year portions of a date.
The syntax for the DateSerial function is:
DateSerial( year, month, day )
year is a numeric value between 100 and 9999 that represents the year value of the date.
month is a numeric value that represents the month value of the date.
day is a numeric value that represents the day value of the date.
TRICK: By setting the day value to zero it will return the date that is the last day of the previous month .
Example Date calculations:
- The first day current month:
DateSerial(Year(Date()), Month(Date()), 1)
- The first day next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)
- The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)
- The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)
- The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)
- The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)
- The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)
- The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)
- The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1
- The last day of the current week:
Date() - WeekDay(Date()) + 7
- The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1
- The last day of the current week:
Date() - WeekDay(Date(), 0) + 7
Examples in a query:
1) In a query you could use this criteria to get all the records with a date in the current month:
Where [MyDateField] Between DateSerial(Year(Date()), Month(Date()), 1) and DateSerial(Year(Date()), Month(Date()) + 1, 0)
2) In a query you could use this criteria to get all the records with a date from the previous month:
Where [MyDateField] Between DateSerial(Year(Date()), Month(Date())-1,1) and DateSerial(Year(Date()), Month(Date()),0)