select records two months back using function? 1

[Dan01]

Hi all, is it possible to select records two months back in a query. Can one select records with date range of >=3/1/04 and <=3/1/31 using a function? The function needs to calculate the monthend. Thanks, Dan.

 

[BobJacksonNCI]

The following should help you get what you want.

MS Knowledgebase 210604-ACC2000 - Functions for Calculating and Displaying Date/Time Values
The information in this article applies to:
· Microsoft Access 2000
· Microsoft Visual Basic for Applications
This article was previously published under Q210604
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY
Because a Date/Time value is stored as a double-precision number, you may receive incorrect formatting results when you try to manipulate Date/Time values in an expression. This article demonstrates how to create expressions and custom functions for displaying specific dates and for calculating time intervals.

MORE INFORMATION
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 the Immediate window to return specific dates:
NOTE: THE FOLLOWING RETURN DATES. The first item below returns the DATE of the first of the month.
· The current month:
DateSerial(Year(Date()), Month(Date()), 1)
· The 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


HTH,
Bob

 

[Dan01]

Bob, thank you very much!! Dan.