Displaying specific dates

To display specific dates, you can use the DateSerial() function to manipulate the day, month, and year portions of a date. For example, you can use the following expressions in the ControlSource property of a text box or in a query to return specific dates:

• 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 of how to used the about code:

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)

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)