Home	Excel	Access
| Feedback | Adding program variable to a query |

A simple Access query to select between a range of dates

Query Parameters
	Parameter	Data type
	Date from	Date/Time
	Date to	Date/Time

Query parameters can filter a dataset to show only those records that meet various conditions based upon dates. In this example three different conditions are being applied:

• between two dates selected by the user
• not more than 10 days old
• before a date specified within the query (1 April 2005)

The menu option Query | Parameters allows you to define the parameters prompts that the user will see and also the data types (i.e. Date/Time). The two user parameters can both be applied to the 'Date_Out' field in order to select a range of dates

Param-Dates01: Select Query
Field:	Booking_Ref	UserGroup_Name	Date_out	Date_Return	Invoice_Date
Table:	Bookings	Bookings	Bookings	Bookings	Bookings
Sort:
Show:	Y	Y	Y	Y	Y
Criteria:			Between [Date from] And [Date to]		>=#01/04/2005#
Or:			Between [Date from] And [Date to]		<Now()-10

Would you like to learn more?

Access 2003 Professional Results

In order to specify a permanent date within a parameter, it must be entered between hash # symbols.

In the example there are two alternative criteria for the Invoice date (after 1st April 2005 and more than 10 days old). This necessitates that the other criteria is repeated so that it gets taken into consideration on both rows. If the 'Date From'/'Date To' parameters were only applied on the first criteria row the records would be filtered so that:

• the value is between the two dates AND after 1st April 2005
  OR
• the invoice date is less than the date 10 days ago

The Now() function returns today's date and therefore Now()-10 is the date 10 days ago.

Office Leave Planner

an Access database for recording and displaying planned staff absences [View details]