Help!! - report to show gpas between dates

[AdrianE]

I have a database where a record has a start date and an end date and is linked to an employee.

Ho do I create a report that will show each employee and gaps between the employees next record start date and end date?

Many thanks, a confuesed Adrian :confused:

[sashi]

Hi there,

below is a simple sample SQL statement dealing with dates.. good luck my fren.. :)

cmd = _
"SELECT * FROM tData WHERE StationID LIKE '*BOOM*'" & _
"  AND UserID = '" & cmbUserID & "'" & _
"  AND DTG > #" & strStartDate & "#" & _
"  AND DTG < #" & _
     Format(DateAdd("d", 1, strEndDate), "short date") & "#" & _
"ORDER BY DTG;"
 

[comteck]

All you have to do is create a query with the table that has the date values. Drag down into the query the fields you want on the report. Of course, the date would be one of them.

Then, in the "Criteria" box for the date, type the following:

Between [Enter Start Date] And [Enter End Date]

This will prompt you to enter a start date and an end date. Of course, in the table, the data type for this date should be "Date/Time". Make sure you check that.

Lastly, in your report, open the properties, and select this query as the Record Source.

Good Luck.
comteck

[AdrianE]

I've created the query which prompts me for the 2 dates but shows all records not just gaps?

E.g. employee will repeat as many times as there are records but doesnt highlite that there is say a one day gap inbetween record 3 and 4 for that employee?? :confused: :confused: :confused:

[comteck]

Sorry. I think I misunderstood what you were asking. And now, I'm confused. I don't quite get what you're saying about the "gap". Could you be more specific, and maybe include some data to give a better idea of what you're asking.

Thanks.
comteck

[AdrianE]

E.g. table Employees: ID, Name, Address etc

Table Vehicles: Reg, Make, Model

I now enter mileage and start to end dates as Entry fields in a table with lookup values of one "vehicle" and one "employee", e.g "003" "Jo Bloggs" drove "VK03 XXX" between "4/04/06" and "12/04/06".

next entry for same employee was "17 April" to "20th April".

I need to somehow distinguish that for any employee ID if there are any gaps. In the instance of "003" " jo Bloggs" there is no record covering dates 13/04/06 to 16/04/06 and possible many more depending on how many enteries?

:confused: