An Access query to select data 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 ribbon option Query Tools Design | Show/Hide | Parameters allows you to define the parameters prompts which are displayed at run time and also the data types (i.e. Date/Time). In this example the two parameters can both be applied to the 'Date_Out' field in order to select a range of dates.

Param-Dates01: Select Query						ox
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

	more...			more ...

In order to specify an absolute date within a criteria, it must be entered between hash # symbols.

In the example the run-time parameters are combined with two additional criteria. The Invoice date must be after 1st April 2005 and more than 10 days old. If the Invoice Date criteria are entered on two rows, the other parameters must also be repeated and entered twice. If the 'Date From'/'Date To' parameters are 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
• all records where the invoice date is less than 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]