Solved

Creating access report with date differences between rows

Posted on 2014-09-08
9
262 Views
Last Modified: 2014-09-09
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
Comment
Question by:rltomalin
  • 4
  • 4
9 Comments
 
LVL 57

Accepted Solution

by:
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

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
 
LVL 34

Expert Comment

by:PatHartman
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

by:rltomalin
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

by:rltomalin
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)

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

End Sub
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 57
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:

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
 

Author Comment

by:rltomalin
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:

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

Regards

Richard
0
 
LVL 57
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

by:rltomalin
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
ID: 40312260
Richard,

 Good to hear!

Jim.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now