| Feedback | Adding program variable to a query | |
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:
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 | ||||
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:
2 | Param-Dates01: Select Query | ||||
Booking_Ref | UserGroup_Name | Date_out | Date_Return | Invoice_Date | |
25610 | Cavaliers club | 12/09/05 | 12/09/05 | 14/09/05 | |
25611 | Chelsfield Guides | 15/09/05 | 16/09/05 | 17/09/05 | |
25612 | HouseMartins | 20/09/05 | 22/09/05 | 24/09/05 |
The Now() function returns today's date and therefore 'Now()-10' is the date 10 days ago.
Office Leave Planner |
an Access database for |
file: access-date-param01.htm | Page last updated Mar14 | © MeadInKent.co.uk 2014 |