Generating a 2 week report with a query.

[MrChris]

Hi all...

Expanding on my operator database I want to run a bi-weekly report for pay purposes.

NeoPa kindly gave me this code when solving my last problem:

SELECT CDate([Enter Date Here :]) AS SelectDate, Jobs.ID, 
       Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], 
       Jobs.[Site Name], Jobs.[Site Address]
FROM Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
WHERE (CDate([Enter Date Here :]) Between [Jobs]![Date Start] And [Jobs]![Date End]);

The code calls on the same tables and field, but I would like the query to pull from a date to another date, picking up each indivdual day that the operator is out, based on the Start and end Date in the Jobs database.

So for example:

If J. Doe has a job on the database Starting from 01/01/07 to 06/01/07 and is it at all possible to have it list the individual days that he was out?

[MrChris]

I've had a think about it and I feel I need to clarify.

I need the same query to work on date ranges,

Whereas this current code asks for one date... I would like it to ask for 2 dates and do the same job, pulling a report as to who is where on each day in the range specifed by the user.

SELECT CDate([Enter Date Here :]) AS SelectDate, Jobs.ID, 
       Ops.[First Name], Ops.[Last Name], Ops.[Mobile Tel], 
       Jobs.[Site Name], Jobs.[Site Address]
FROM Ops INNER JOIN Jobs ON Ops.ID = Jobs.ID
WHERE (CDate([Enter Date Here :]) Between [Jobs]![Date Start] And [Jobs]![Date End]);

[NeoPa]

This is not so straightforward :(
Here (Find Missing Dates in Query) is a link to a thread I'm currently involved with that deals with just this issue. It will certainly help, if only to help you understand better how complicated it is ;)

[MrChris]

Crikey that certanly looks like like a right old head scrather...

Maybe I can simplify my question then.

Can it be done do that when I enter the date into the query (as before) and it does what it did before... but for a flat 14 days from the date specified

so if I enter 01/01/07 it displays from 01/01/07 to 14/01/07?

[NeoPa]

This would need the same basic construct I'm afraid. There are probably ways to do this in a code loop but to use SQL the dates need to be available in a data source (Table) first, before they can be accessed.