Filter??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a report that lists Driver License expiration
dates that are 3 months out from the month that the report is opened.

I opened the table and have played with the filter, but:

1. what would be my "criteria" for the filter based on "todays" month
to include all DL expirations in the 3rd month from "todays" month?
If I open the report in January, then the criteria would automatically
select all the DL expirations in April.

2. How do I setup a "report" to use the query?

Tom
 
Tom said:
I want to create a report that lists Driver License expiration
dates that are 3 months out from the month that the report is opened.

I opened the table and have played with the filter, but:

1. what would be my "criteria" for the filter based on "todays" month
to include all DL expirations in the 3rd month from "todays"
month? If I open the report in January, then the criteria would
automatically select all the DL expirations in April.

2. How do I setup a "report" to use the query?

Tom
Click to expand...

Substitute Date() with your date field.
Would be 3 months prior to Date()
DateSerial( Year( Date() ), Month( Date() ) -3, Day( Date() ) )
 
setting a filter at the table level will not help you. you need to either
set criteria in the report's underlying query, or set a filter in the report
itself, or - if you're opening the report from a form with an OpenReport
action in macro or VBA - set criteria in the WHERE clause action argument.

let's say that the expiration date field in your table is called ExpirDate.
in a query, add a calculated field, as

ExpirMonth: Month([ExpirDate])

if you don't want the calculated field to show in the query's dataset, then
uncheck the checkbox in the "Show" row of that column. set the following
criteria on the ExpirMonth calculated field, as

Month(Date())+3

to set a filter in the report, or in an OpenReport action's WHERE clause
argument, use

Month([ExpirDate]) = Month(Date())+3

the syntax will vary somewhat, depending on whether you're using a macro or
VBA code. note that the rule of thumb is to "weed out" unwanted records as
close to the source as possible; suggest that you set criteria in the query
itself, rather than waiting until the report opens to filter the query
dataset.

hth
 
Criteria under the field would be

Field: YourFieldName
Criteria: Between DateSerial(Year(Date()),Month(Date())+3,1) And DateSerial(Year(Date()),Month(Date())+4,0)

So for today, Dec 18, 2005 that becomes

Between #2006-03-01# and #2006-03-31#
 
Back
Top