rltomalin
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
dPrev = Null
End Sub
Private Sub GroupHeader0_Format(Cancel
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
' Company name header
Me.CompanyName = DLookup("CompanyName", "tblParameters")
End Sub
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"
https://www.experts-exchange.com/Database/MS_Access/VP_536.html
Jim.
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"
https://www.experts-exchange.com/Database/MS_Access/VP_536.html
Jim.
ASKER
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.
(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.
ASKER
Hi Jim
Thanks for this. I have now got it all figured out and working.
Thanks for your excellent and concise responses.
Regards
Richard
Thanks for this. I have now got it all figured out and working.
Thanks for your excellent and concise responses.
Regards
Richard
Richard,
Good to hear!
Jim.
Good to hear!
Jim.
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