Access

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 .

• 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)