Calculation of values in the same field

[tkoh78]

Hi, maybe I am searching for wrong words. Hopefully this question has not been asked.

I have values in column. I want to know if there is a way to do calculation of those values.
Example:

Date
1/1/07
2/4/07
3/2/07

I want to show days elapsed showed in the report. So, from 1/1/07 to 2/4/07, it would show 35, and from 2/4/07 to 3/2/07, it would show 26, and so forth.

Thanks in advance.

 

[Dennisk]

use the datediff function

 

[Peter Reid]

Add an unbound field in the detail section of your report called 'PreviousDate'

Add a group footer for your date field (you don't have to display this footer) and add some code in the 'On Format' event of this along the lines of;

Me.PreviousDate = Me.Date

You'll then be able to add a calculated field in the detail section with the control source as;

=IIf([Date]>[PreviousDate],[Date]-[PreviousDate],"")

{the iif statement has been added because the first 'PreviousDate' is the last date in 'Date' column so will be negative - this merely blanks the first 'elapased days'}

 

[tkoh78]

Thanks Dennisk..
However, if I understood correctly, in order to use datediff function, I need to have values in two columns. However, I have all my values in one field, namely Dates.

Thanks Peter,
Unfortunately, I did not understand "Me.Previousdate = Me.Date" code.
What is "Me"? I guess Date would be my data of dates, but what data would be in PreviousDate text box?

Thank you in advance

 

[Peter Reid]

Thanks Dennisk..
Unfortunately, I did not understand "Me.Previousdate = Me.Date" code.
What is "Me"? I guess Date would be my data of dates, but what data would be in PreviousDate text box?

Thank you in advance

Not being formally trained in Access, I thought I'd look up a definition for "me";

"Me" refers to the currently active object in VBA class module code (a
Form's code or a Report's code is a class module), thus for a Form named
frmYours, with a TextBox named txtYours, "Me.txtYours" in the form's module
is equivalent to "Forms!frmYours.txtYours".

Larry Linson - Microsoft Access MVP

Therefore, Me.Date will reference the date field on your report (assuming yor date field name is 'Date')

The PreviousDate field will initially have no data but will be set whenever the group footer is encountered using the code Me.PreviousDate = Me.Date

I did test this before originally replying so I can post an example mdb if you like (if I can find it!)

 

[tkoh78]

hmm.. still problem

Thanks again Peter.

I am still having problem. Could you send me an example?
When I put Me.PrviouseDate=Me.Date, the error message pops up saying that Me macro or module does not exist. Maybe I am doing something wrong.
I am attaching a simple version of database that shows the report that I am creating.

Thanks you!!

 

[Peter Reid]

Thanks again Peter.

I am still having problem. Could you send me an example?
When I put Me.PrviouseDate=Me.Date, the error message pops up saying that Me macro or module does not exist. Maybe I am doing something wrong.
I am attaching a simple version of database that shows the report that I am creating.

Thanks you!!

You had only added an unbound field to the report (and not named it PreviousDate - this was why you had an error)

The footer also had to be added as had the code to set PreviousDate = ReadingDate

The attached includes this (I've added the items in bold). Are the readings 'meter readings'? If so, you could add a 'usage' calculation to show the difference in readings between the 2 dates by doing a similar thing