Solved

# Creating access report with date differences between rows

Posted on 2014-09-08
276 Views
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
0
Question by:rltomalin
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40309839
I think I'd do it in the report.

In the reports module, declare a variable:

Dim dtLastJobDate as Date

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

LVL 37

Expert Comment

ID: 40309860
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

Author Comment

ID: 40310004
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

Author Comment

ID: 40311694
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)

Me.CompanyName = DLookup("CompanyName", "tblParameters")

End Sub
0

LVL 57

Expert Comment

ID: 40311870
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:

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

Author Comment

ID: 40312016
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:

and that gives this after clicking debug

Hope this makes sense.

Regards

Richard
0

LVL 57

Expert Comment

ID: 40312118
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

Author Closing Comment

ID: 40312189
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

LVL 57

Expert Comment

ID: 40312260
Richard,

Good to hear!

Jim.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
###### Suggested Courses
Course of the Month6 days, 3 hours left to enroll