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

Reference records in query/report and compare against previous record

P: n/a
I have a database report that is based on a query that returns actions
taken on certain dates. The data looks like this: (The report based on
this query is layed out similarly)

Date Action1 Action2 Action3
9/1/06 2 4 4
9/8/06 2 4 4
9/15/06 2 4 4
9/25/06 2 4 4

I need to be able to generate an "interval" field that tells the number
of days between each action in the query or on the report that looks
like this:

Date Action1 Action2 Action3 Interval
9/1/06 2 4 4
9/8/06 2 4 4 7
9/15/06 2 4 4 7
9/25/06 2 4 4 10

I can't figure out how to reference the dates within the query to be
able to compare them against each other. Any help would be greatly
appreciated.

Sep 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi,

I had a quick try and the code underneath seems to work, simply by
putting it
into the report and adding a textbox (text5) in the detail section of
the report.

Option Compare Database
------------------------------------------------------------------------------------------------------
Dim skiponce As Boolean
Dim prevdate As Date
------------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
skiponce = True
End Sub
------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If skiponce Then
Text5 = 0: skiponce = False
Else
Text5 = DateDiff("d", prevdate, actiondate)
End If
prevdate = actiondate
End Sub

Success!
Rudi.

Sep 28 '06 #2

P: n/a
Hi,

just put following code into the report and add a text box in the
report detail section called "text5". Seems to do the job.

Option Compare Database
------------------------------------------------------------------------------------------------------------------------
Dim skiponce As Boolean
Dim prevdate As Date
------------------------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If skiponce Then
Text5 = 0: skiponce = False
Else
Text5 = DateDiff("d", prevdate, actiondate)
End If
prevdate = datum
End Sub
------------------------------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
skiponce = True
End Sub

Success!
Rudi.

Sep 28 '06 #3

P: n/a
Outstanding!! Thank you so much! It works perfectly.

John

Rudi wrote:
Hi,

I had a quick try and the code underneath seems to work, simply by
putting it
into the report and adding a textbox (text5) in the detail section of
the report.

Option Compare Database
------------------------------------------------------------------------------------------------------
Dim skiponce As Boolean
Dim prevdate As Date
------------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
skiponce = True
End Sub
------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If skiponce Then
Text5 = 0: skiponce = False
Else
Text5 = DateDiff("d", prevdate, actiondate)
End If
prevdate = actiondate
End Sub

Success!
Rudi.
Sep 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.