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

Minggu, 05 Januari 2014

Re: [MS_AccessPros] Getting "Balance" query

John,

I have tried this code like this:

Private Sub TransactionDate_GotFocus()

' 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

End Sub

Query is the first one which you are saying should work unless there are null values in TransactionID field.

Now with this code i dont have null value in TransactionID field, BUT still not getting value in "balance"

Khalid

On Monday, January 6, 2014 11:37 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

First of all i should mention, that query was other database "PCTL" and now i working on other database "PCTL Accounts".

Yes there would be a null value if it is the first entry on this form that was the reason i mentioned about the code:

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

The reason why i included this code is with reference to my previous thread in which you suggested this code on 2 Jan see below:

John Viescas

To MS_Access_Professionals@yahoogroups.com

Jan 2 at 5:45 PM

Khalid-

First of all, there is a function called IsNothing, but it requires an object as an argument, not a field or control.

As the article implies, you first have to change your AutoNumber field to Long Integer, but I recommend you make a backup copy of the table first.

Your code needs to go in the BeforeUpdate event of the form. It should look something like this:

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", "Transactions"), 0) + 1

End If

End Sub

… where "Transactions" is the name of your table.

Hope you can understand me now.

Khalid

On Monday, January 6, 2014 2:15 AM, John Viescas <JohnV@msn.com> wrote:

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