Dates in a query

[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

 

[Douglas J. Steele]

Your Where clause would be something like

WHERE StartDate <= Date()
AND Nz(EndDate, Date()) >= Date()

 

[SJL]

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

 

[Douglas J. Steele]

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()

 

[SJL]

Excellent. Thanks.

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()


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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