MeadInKent
| Feedback | Adding program variable to a query |

An Access query to select data between a range of dates

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

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
  bookings table
 
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  
 

 
UK more...   USA 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:

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
office leave planner

an Access database for
recording and displaying
planned staff absences

[View details]



file: access-date-param01.htm Page last updated Mar14 MeadInKent.co.uk 2014