Datasheet, How do I figure record differences???

[TheRealDeal]

I want to figure the difference between 2 fields from different records of the same table. I'm viewing the table via a datasheet with all controls bound except for the results textbox.

a picture:
ID(B) Field2(B) Field3(N)
1 2000 0
2 3400 1400
3 5000 1600

and so on... in essense, Field3 is Not-Bound and contains the difference of current record and the previous. My goal is some function or subroutine

 

[GingerR]

i made a query with this calculated field:

Diff: [Field2]-DLookUp("Field2","Table","[ID]=" & [ID]-1)

where 'Table' = the name of my table. ID and Field2 set up as you describe above.

the dlookup line is getting the previous data in Field2 (the data at ID-1).

g

 

[RichUK]

Check out the PrevRecVal function it's more efficient than DLookUp

 

[MichaelRed]

RichUK,

PrevRecVal doesn't appear in my hepl. Could you post the syntax for the function and how to find it in the help system?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[RichUK]

I can't remember where I found it but I'm certain it's from MS, I've altered it slightly.
Function PrevRecValc(KeyName As String, KeyValue, _
FieldNameToGet As String, Source As String)
Dim RS As Recordset

On Error GoTo Err_PrevRecValc

' The default value is zero.
PrevRecValc = 0


Set RS = CurrentDb().OpenRecordset((Source), dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?

Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"

Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecValc = RS(FieldNameToGet)

Bye_PrevRecValc:
Exit Function
Err_PrevRecValc:
Resume Bye_PrevRecValc
End Function

 

[RichUK]

The original's here Article ID: Q101081

 

[MichaelRed]

Thanks.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over