how do I calculate the change in a value between 2 Access records

[RickSF]

I have a form containing a table's history of prices by date. I want to
calculate the change in price from one record to the next. How do I
calculate the change in a value between 2 Access records? records can be
deleted from the table, so the calculation should always reference the next
record and show the calculated change.

Thanks for your help.

 

[Klatuu]

How do you determine the "next" record?
Since form filtering and ordering will affect the recordset, you can have a
difficult time determining which record to use. If you have a rule that will
determine what the next record is, you can use an expression using a DLookup
as the control source of a text box to display the value:

=DLookup("[Price]", "TableName", Criteria for FindingRecord)-[txtPrice]

 

[RickSF]

Thanks. I tried the DLookUp function but I keep getting "#Name?" in the text
box. Here is the syntax:
=DLookUp([Price],[UpdateHistoryTbl],[RecordDate]=[PriorDate])

All I want to do it display the prior Price in the text box from the
PriorDate field I have selected.

I appreciate your help.

Rick

How do you determine the "next" record?
Since form filtering and ordering will affect the recordset, you can have a
difficult time determining which record to use. If you have a rule that will
determine what the next record is, you can use an expression using a DLookup
as the control source of a text box to display the value:

=DLookup("[Price]", "TableName", Criteria for FindingRecord)-[txtPrice]
--
Dave Hargis, Microsoft Access MVP

I have a form containing a table's history of prices by date. I want to
calculate the change in price from one record to the next. How do I
calculate the change in a value between 2 Access records? records can be
deleted from the table, so the calculation should always reference the next
record and show the calculated change.

Thanks for your help.

 

[Klatuu]

Your syntax is incorrect
=DLookUp([Price],[UpdateHistoryTbl],[RecordDate]=[PriorDate])
Should be
=DLookUp("[Price]", "[UpdateHistoryTbl]", "[RecordDate] = #" & [PriorDate]
& "#")

I assume RecordDate is a field in the table UpdateHistoryTbl and PriorDate
is a control on your form and that [RecordDate] is a date data type.
--
Dave Hargis, Microsoft Access MVP

Thanks. I tried the DLookUp function but I keep getting "#Name?" in the text
box. Here is the syntax:
=DLookUp([Price],[UpdateHistoryTbl],[RecordDate]=[PriorDate])

All I want to do it display the prior Price in the text box from the
PriorDate field I have selected.

I appreciate your help.

Rick

How do you determine the "next" record?
Since form filtering and ordering will affect the recordset, you can have a
difficult time determining which record to use. If you have a rule that will
determine what the next record is, you can use an expression using a DLookup
as the control source of a text box to display the value:

=DLookup("[Price]", "TableName", Criteria for FindingRecord)-[txtPrice]
--
Dave Hargis, Microsoft Access MVP

I have a form containing a table's history of prices by date. I want to
calculate the change in price from one record to the next. How do I
calculate the change in a value between 2 Access records? records can be
deleted from the table, so the calculation should always reference the next
record and show the calculated change.

Thanks for your help.

 

[Douglas J. Steele]

Yes, mm/dd/yyyy is one of the formats that Access will always recognize
correctly. (yyyy-mm-dd is another, as is dd mmm yyyy)

To be safe, it's best to use

=DLookUp("[Price]","[UpdateHistoryTbl]","[RecordDate] = " & _
Format([PriorDate], "\#yyyy\-mm\-dd\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi -

The DLookup function requires string type parameters. Try this:

=DLookUp("[Price]","[UpdateHistoryTbl]","[RecordDate] = #" & [PriorDate] &
"#")

This assumes that [priordate] is in a format the Access can understand (I
*think* it needs to be mm-dd-yyyy, but I could be wrong). It also assumes
that [Price] and [UpdateHistoryTbl] are the actual field and table names.

John

Thanks. I tried the DLookUp function but I keep getting "#Name?" in the
text
box. Here is the syntax:
=DLookUp([Price],[UpdateHistoryTbl],[RecordDate]=[PriorDate])

All I want to do it display the prior Price in the text box from the
PriorDate field I have selected.

I appreciate your help.

Rick

How do you determine the "next" record?
Since form filtering and ordering will affect the recordset, you can
have a

[quoted text clipped - 11 lines]

Thanks for your help.