Creating access report with date differences between rows

I need to create a report and I really can't quite get my head round how to start.

I have two tables - tblClient and tblJobs
These two tables are linked via ClientID

My basic task is to get a report that displays the difference in time between successive jobs.
So - something like this:
Client1
Job1    date
Job2    date    diff
Job3    date    diff
job4    date    diff

Client2
Job1    date

Client3
Job1    date
Job2    date    diff
etc

The [diff] is the period in days between the job and the previous job.

I have produced the query to produce the job list.  I can easily create a report to list the jobs, grouped by Client.
But I can't figure out how to calculate the difference figure.  EG - how to refer to the previous job's date in the calculation.

Any hints would be most welcome.

Regards
Richard
rltomalinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think I'd do it in the report.

In the reports module, declare a variable:

Dim dtLastJobDate as Date

In the Clients Header:

 dtLastJobDate  = 0

Then in the Details OnFormat do the calculation:

 Me.txtDateDiff = DateDiff("d",dtLastJobDate,Me.txtCurrentJobDate)

and in the Details OnPrint:

  dtLastJobDate = Me.txtCurrentJobDate

 You'll need to adjust the above for your actual control names, but it gives you the idea.

 Also, your going to need some logic to address what happens on the first job for each client.  Maybe:

 If dtLastJobDate = 0 then
   ' Do nothing, first job for client
 Else
   Me.txtDateDiff = DateDiff("d",dtLastJobDate,Me.txtCurrentJobDate)
 End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Create a variant variable that is public to all events of the report.  
dim dPrev as Variant

In the Form's Open event, populate it with the value of the first record for the client.

dPrev = Me.JobStartDT

In the Format event for the Footer for the client section, clear the variable
dPrev = Null
In the Format event for the Header for the client section, populate the variable if it is null
If IsNull(dPrev) then
    dPrev = Me.JobStartDT
End If

Then in the Format event of the detail section, populate the diff field and update the variable.

Me.NumDays = datediff("d", dPrev, Me.JobStartDT)
dPrev = Me.JobStartDT
0
rltomalinAuthor Commented:
Thanks guys
Reading through, both the options look similar.  I will have a look tomorrow.
I have never done any coding like this in a report, so I guess it will take a while.  Will keep the question open for now, ready for maybe some further questions.

Regards
Richard
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rltomalinAuthor Commented:
Hi Guys
I think I may be getting somewhere with this.  My problem though (being a VB novice) is that I do not know exactly how to create/declare the variable in the first place.
Both of the options look very similar and include this first step.
Below is the code that I have for the report at the moment.

Can you please let me know how to declare the variable - the report does of course error because it says the variable in the Report Open event has no value.

Regards

Richard

Option Compare Database

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
Me.NumDays = DateDiff("d", dPrev, Me.[Date completed])
 dPrev = Me.[Date completed]
End Sub

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
dPrev = Null
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(dPrev) Then
     dPrev = Me.[Date completed]
 End If
End Sub

Private Sub Report_Open(Cancel As Integer)
dPrev = Me.[Date completed]
End Sub

Private Sub ReportHeader3_Print(Cancel As Integer, PrintCount As Integer)

' Company name header
Me.CompanyName = DLookup("CompanyName", "tblParameters")

End Sub
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Richard,

When you open the report in design view, and then go to the VBA code for the report, you're in the reports module.

Forms and reports are actually class objects and they have their own module attached to them.

Variables that apply to the entire module go at the top in the declarations section, which is outside of any procedure.   They can be declared as Private, meaning they are only available to the procedures in the module and can't been seen outside of the module, or public meaning they can be seen outside of the module.

 All the form and report properties you see with Access are actually variables that have been declared public in a special way.

If a variable is inside of a procedure, then it is only available to that procedure, and it disappears when the procedure finishes, unless it has been declared Static, so it retains it value call to call.

 There's actually a little more to it then that, but suffice to say that you want this at the top, declared private, and since your using NULL in it, then it needs to be a variant, which would look like this:

Variable declare screenshot
With your naming convention however, if "d" stands for date, I would change that to "v" or "var" as your really working with a variant.   Variants can hold any data type, so it's really not a date.

What I did was use the base date of (date type with a value of 0)  to indicate "no date".  That's really not kosher though because zero is a valid date.   However the chance is slim that someone would actually request a date that includes the base date of 12/30/1899 and I like the strong typing I get by specifying a date variable type.   If I was really doing it right though, I should have used a second variable to indicate "no date".

When you use the variant type, you can stuff anything into it and if your not careful, can end up with bugs through type conversions that occur which you might not be aware are happening. But a variant lets you get away with using a single variable as it's the only one that can accept a NULL, which you can use as a "no date" flag.

One other thing; get used to adding "Option Explicit" at the top of every module you work with.  This means that you must explicitly declare every variable you use and it's a good way of preventing bugs.

For that and other tips, you may want to check out "Writing Solid VBA Code"

http://www.experts-exchange.com/Database/MS_Access/VP_536.html

Jim.
0
rltomalinAuthor Commented:
Hi Jim
Thanks for the comprehensive feedback.  I have made the changes you suggest but am still getting an error when I try to run the report:

Error notification
and that gives this after clicking debug
VBA with error highlighted
Hope this makes sense.

Regards

Richard
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Is [Date Completed]

 (including the space, which is not a good idea to use by the way), a control on your report?   It can be hidden, but it must be a control, not a field.

Jim.
0
rltomalinAuthor Commented:
Hi Jim
Thanks for this.  I have now got it all figured out and working.
Thanks for your excellent and concise responses.

Regards
Richard
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Richard,

 Good to hear!

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.