Field from Previous Record

[Humberto]

Hello, I'm trying to run a query were I get the running total (runttl)from a calculated field and I want to get the running total (runttl)from the previous record and add it and display it in the current record. when I try this

SELECT DLookUp("[runttl]","cash disbursment","[id]>=" & [id]-1) AS PreviousBalance, [Cash Disbursment].id, [Cash Disbursment].Date, [Cash Disbursment].MoneyOrder, [Cash Disbursment].CashIn, [Cash Disbursment].TellerDrawer, [Cash Disbursment].CashOut, [Cash Disbursment].ATMIn, [Cash Disbursment].ATMOut, [Cash Disbursment].LooseCoin, [Cash Disbursment].MuteSold, [moneyorder]+[cashin]-[cashout]+[atmin]-[loosecoin]-[atmout]-[mutesold] AS subtotal, [subtotal]+[tellerdrawer]+[runttl] AS Totals, Val(DSum("[moneyorder]+[cashin]-[cashout] +[atmin] -[loosecoin]-[mutesold]-[atmout]","Cash disbursment","[id] <= " & [id] & "")) AS runttl, DLookUp("[runttl]","Cash Disbursment","[ID] = " & [ID]-1) AS PreviousTTL
FROM [Cash Disbursment];

it gives me zeros on the field that I want from the previous record (PreviousTTL). I don't know if it's because runttl is a calculated field. can some one help me I will really appreciate it.

 

[Rich]

Can't you do this at form or report level? It's much easier

 

[Humberto]

Thank you for responding. I tried it in a form, but does not work. this is what I tried:
PreviousTTL: DLookUp("[runttl]","Cash Disbursment","[ID] = " & [ID]-1)

and this:

PreviousTTL: DLookUp("[moneyorder]+[cashin]-[cashout] +[atmin] -[loosecoin]-[mutesold]-[atmout]","Cash Disbursment","[ID] = " & [ID]-1)

but on both examples it gives this: #Name?

I also tried this:

=DLookUp("[moneyorder]","cash disbursment]","[id]=forms![cash disbursment input form]![id]")

but this gave me an error. #Error

any ideas?

Thank you

 

[Rich]

I prefer to use this function at form level
Function RunSum(F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'***********************************************************
Dim rs As Recordset
Dim result

On Error GoTo Err_RunSum

' Get the form Recordset.
Set rs = F.RecordsetClone

' 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!"
GoTo Bye_RunSum
End Select

' Compute the running sum.
Do Until rs.BOF
result = result + rs(FieldToSum)

' Move to the previous record.
rs.MovePrevious
Loop

Bye_RunSum:
RunSum = result
Exit Function

Err_RunSum:
Resume Bye_RunSum

End Function
HTH

 

[Humberto]

thanks for the code, but the running Total is running fine. what I need to do is get the running total from the previous record and put it in the current record. can you help me with this.

thanks

 

[Rich]

Look up the PrevRecVal function

 

[Jon K]

Attached is a demo DB, which contains a simplified version of your table and the following query:-

SELECT ID, Date, CashIn, CashOut, ATMIn, ATMOut,
Nz(CashIn)-Nz(CashOut)+Nz(ATMIn)-Nz(ATMOut) as DailyBalance,
(Select Sum(Nz(CashIn)-Nz(CashOut)+Nz(ATMIn)-Nz(ATMOut)) from [Cash Disbursment] where ID <= a.ID) as DailyRunningSum,
(Select Sum(Nz(CashIn)-Nz(CashOut)+Nz(ATMIn)-Nz(ATMOut)) from [Cash Disbursment] where ID = a.ID-1) as PreviousDailyBalance,
(Select Sum(Nz(CashIn)-Nz(CashOut)+Nz(ATMIn)-Nz(ATMOut)) from [Cash Disbursment] where ID < a.ID) as PreviousRunningSum
FROM [Cash Disbursment] as a


When the query is run, it returns four calculated fields:-
DailyBalance, DailyRunningSum, PreviousDailyBalance, PreviousRunningSum

Hope you can adapt it to suit your needs.

 

[Humberto]

Thanks for all your help guys, it worked.