deducting the previous month from the current month

C

clive jenkins

I have a table with the following data

month reading
Jan 100
Feb 200
mar 300

I need to take the value from a current month and deduct the previous month.
This needs to be done for all of the records within the field
 
R

Rick B

You can do so right there on the Microsoft Support page. There should be a
search button. You can also search Google or similar.
 
C

clive jenkins

Thanks Rick B

PS do you know how to do this

Rick B said:
You can do so right there on the Microsoft Support page. There should be a
search button. You can also search Google or similar.
Click to expand...
 
R

Rick B

In a Form
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Forms![MyForm]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Forms![MyForm]![ID]+1")
In a Report
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Reports![MyReport]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Reports![MyReport]![ID]+1")
In a Query
To obtain a value from a field in the previous record, type the following
line in the Field row of the query grid:
Expr1: DLookUp("[MyField]","MyTable","[ID]=" & [ID]-1)
To obtain a value from a field in the next record, type the following line
in the Field row of the query grid:
Expr1: DLookUp("[MyField]","MyTable","[ID]=" & [ID]+1)
 
C

clive jenkins

Hello Rick B

Tried the search but didnt come back with anything. any sudgestions on the
search criteria?
 
C

clive jenkins

Hello Rick B

Thats great thanks for the help "Much Appreciated" :)

Rick B said:
In a Form
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Forms![MyForm]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Forms![MyForm]![ID]+1")
In a Report
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Reports![MyReport]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[MyField]","MyTable","[ID]=Reports![MyReport]![ID]+1")
In a Query
To obtain a value from a field in the previous record, type the following
line in the Field row of the query grid:
Expr1: DLookUp("[MyField]","MyTable","[ID]=" & [ID]-1)
To obtain a value from a field in the next record, type the following line
in the Field row of the query grid:
Expr1: DLookUp("[MyField]","MyTable","[ID]=" & [ID]+1)



clive jenkins said:
Thanks Rick B

PS do you know how to do this
Click to expand...
Click to expand...
 
J

John Vinson

Hello Rick B

Tried the search but didnt come back with anything. any sudgestions on the
search criteria?
Click to expand...

Rick's making the (possibly incorrect) assumption that you have a
numeric field named ID in your table, and that the ID for Jan is
reliably exactly one less than the ID for Feb. You can't count on that
unfortunately!

The fact that you're storing the month as a text string makes this
more complex: "Feb" is just a string of letters to Access, and "Feb"
comes before "Jan" since text fields are sorted alphabetically. Would
there be any chance of changing this field to a Date/Time field? You
could set the Format of the field to

"mmm"

to display just Feb, while actually storing #02/01/2004#. Then you
could use the DateAdd function to link each record's value to the
previous month's value (and would be able, as you cannot now do, to
link to December 2004's value from January 2005).

John W. Vinson[MVP]
 
Top