ACCESS FEED: Re: [MS_AccessPros] Getting "Balance" query

Minggu, 05 Januari 2014

Re: [MS_AccessPros] Getting "Balance" query

Khalid-

The first query should work unless there are Null values in the TransactionID field in any of the rows.

And your code is wrong. It should be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' If on a new record,
If Me.NewRecord Then
' Assign the "next" value to the ID
Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
End If
'-------------------------
Dim msg As String
msg = msg & "Do you want to Save this record?"
If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbNo Then
Cancel = True
Me.Undo
End If
End Sub

I have no clue why you included the code - it has nothing to do with the query.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 5, 2014, at 5:44 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,
Sometime in the near past i had a problem to get balance on a continuous form where i had "TransactionID" field, key, Auto Number, which was resolved by John thanks to him again.
Which was:
SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID])-DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS Balance
FROM ManagersAccounts
ORDER BY ManagersAccounts.TransactionID;

But now i have an other form where TransactionID field is key, Number, Long Integer, and forms before update is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' If on a new record,
If Me.NewRecord Then
' Assign the "next" value to the ID
Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1
End If
'-------------------------
Dim msg As String
msg = msg & "Do you want to Save this record?"
If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbYes Then
DoCmd.Save
Else
Me.Undo
End If
End Sub

While using the above query i get error message:
Syntex error (missing operator) in expression 'TransactionID <='.

this error message has to be clicked two times on OK button.

I tried this modification in the query and i do not get error message but output is not correct in balance i get the same figure on each row which was on first row balance:

SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID")-DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS Balance
FROM ManagersAccounts
ORDER BY ManagersAccounts.TransactionID;

Need help how to modify it.

Khalid