Dates in a query

  • Thread starter Thread starter SJL
  • Start date Start date
S

SJL

Hi
I have a database detailing start date and end dates of temporary staff. My
question is how do I set up a query that will show how may temp staff I have
at any given date. e.g. If I wanted to find out how many temp staff I had on
staff right now?

Thanks
 
Your Where clause would be something like

WHERE StartDate <= Date()
AND Nz(EndDate, Date()) >= Date()
 
Hi Douglas
Thanks for your prompt response. Where do I put the where clause in though?
This is where I show my ignorance... is it in design view of query?

Stu
 
You can work directly with the SQL generated by Access, or you can work with
the graphical query builder.

If you choose the latter, ensure that StartDate and EndDate are both in the
grid. In an empty cell on the Field row, put Nz([EndDate], Date()), and
uncheck the Show box underneath it.

As the criteria under StartDate, put <= Date().

As the criteria under Nz([EndDate], Date()), put >= Date()
 
Excellent. Thanks.

Stu

Douglas J. Steele said:
You can work directly with the SQL generated by Access, or you can work with
the graphical query builder.

If you choose the latter, ensure that StartDate and EndDate are both in the
grid. In an empty cell on the Field row, put Nz([EndDate], Date()), and
uncheck the Show box underneath it.

As the criteria under StartDate, put <= Date().

As the criteria under Nz([EndDate], Date()), put >= Date()


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


SJL said:
Hi Douglas
Thanks for your prompt response. Where do I put the where clause in
though?
This is where I show my ignorance... is it in design view of query?

Stu
Click to expand...
Click to expand...
 
Back
Top