Easy? Calculating b/w records (1 Viewer)

kalicinski

New member
Local time
Today, 12:21
Joined
Jul 16, 2001
Messages
9
SITUATION: consumption rates of water taken from meter readings. Want to know daily consumption based on today's reading subtract yesterday's reading. (Each reading is in it's own record. Readings are not always taken daily so cannot use <date> as a criteria.) Tried using crosstab query but to no avail...
-> Sub question - want to use these calculated values in a form, and want to plot daily consumption on a graph.

[if i'm being too vague, pls advise...]
tks.
 

kalicinski

New member
Local time
Today, 12:21
Joined
Jul 16, 2001
Messages
9
For some reason i cannot find "PrevRec" function in the Help (Access or VB). I can find "FindPrevious,MovePrevious, Seek", but no PrevRec.
What does this function do?
Next suggestion?
 

Chris RR

Registered User.
Local time
Today, 07:21
Joined
Mar 2, 2000
Messages
354
What do you want to do if there is no reading for the previous day?

One option might be to put the data into a recordset, then use the FindPrevious method. Try using the VB help, not the regular Access help.
 

kalicinski

New member
Local time
Today, 12:21
Joined
Jul 16, 2001
Messages
9
If there wasn't a reading for the previous day, then it can take the difference between the two readings anyway. (for simplicity's sake)
Any suggestions on what exactly i should have the FindPrevious search for?
 
R

Rich

Guest
This may help
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.

' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************

Function PrevRecVal(KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = CurrentDb().OpenRecordset("tblName", 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.
PrevRecVal = RS(FieldNameToGet) + 1

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function
 

kalicinski

New member
Local time
Today, 12:21
Joined
Jul 16, 2001
Messages
9
Hello Rich! Thanks for your help with this problem i have.
I tried putting your code into a module in my db. (called it "modPrevRecVal") Then in the text box in my form i made its controlsource =PrevRecVal("tblFX13","subfrmFX13","Date",[Date],"TroughRead")

But the thing is giving me an error [Invalid Argument] when it tries to set the recordset. this is the line: Set RST = CurrentDb.OpenRecordset(tblName, dbOpenDynaSet)
if this line works, the FindFirst line errors [Method or Data Label not found].
Is there a certain Reference that I need to have installed in order to use the recordset methods?
right now I have the following references loaded:
Visual Basic for Applications
Microsoft Access 9.0 Object library
OLE Automation
Microsoft ActiveX Data Objects 2.1 library


thankyou,

i truly appreciate your help!

[This message has been edited by kalicinski (edited 08-15-2001).]
 

Users who are viewing this thread

Top Bottom