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
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar