By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

Help!! - report to show gpas between dates

P: 12
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:
Jul 7 '06 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,754
Hi there,

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

Expand|Select|Wrap|Line Numbers
  1. cmd = _
  2. "SELECT * FROM tData WHERE StationID LIKE '*BOOM*'" & _
  3. "  AND UserID = '" & cmbUserID & "'" & _
  4. "  AND DTG > #" & strStartDate & "#" & _
  5. "  AND DTG < #" & _
  6.      Format(DateAdd("d", 1, strEndDate), "short date") & "#" & _
  7. "ORDER BY DTG;"
Jul 7 '06 #2

P: 179
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.
Jul 9 '06 #3

P: 12
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:
Jul 11 '06 #4

P: 179
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.

Jul 12 '06 #5

P: 12
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?

Jul 26 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.