Using dates in query

  • Thread starter Thread starter cornish
  • Start date Start date
C

cornish

I have an employee data base which has as one field the date the employee
finished employment. I need my query to select only employees that are
currently employed (in which case the field will be blank), or have only
finished employment in the last week or so. In other words the query will
exclude employees that have finished before a particular date (which varies
from week to week). I want to be able to vary the date at least once a week
for report purposes, but also more often as required. Any suggestions will
be appreciated.

--
Doug McKean
Office Manager
R J Cornish & Co Pty Ltd
www.rjcornish.com
Phone (03) 5872 2055
 
You could set up your query to accept a date parameter.
You would be prompted to enter a date and could select all
employees with finished date less than or equal to that
date.

Or if you don't want to enter a date, you could build an
expression. You could use the DATEADD function, along
with DATE() function, to subtract a number of days/weeks
from today's date.

Hope this helps.
 
In the Column / Field for finished date, enter the criteria:
[Enter Finish Date:] OR Is Null
Click to expand...

The first condition selects employees that finished after the date entered
and the second condition selects current employees.

--
HTH
Van T. Dinh
MVP (Access)
 
Doug,

In the query you are using add the following two lines as criteria
under the field containing the date left..

Is Null

[tblname]![fldname] < [Enter Date]

tblname is the name of your table and fldname is the name of the date
field relating to when the employee left.

When you run the query you get prompted for [Enter Date], enter the
date you want. You will then get a list of employees who have not
left (is null) and those who left before the date you entered.

Hope this Helps

David A Mitchell
Share What You Know, Learn What You Don't
 
Try creating a parameter query. I would suggest the
following syntax:
=[Enter Date]
Click to expand...

For the query for current employees, you are trying to
pull from a blank field correct? If that is so, I would
use the criteria to be Is Null.

When you open the query/report it will prompt you each
time to enter a date.
 
Back
Top