Creating a Running Balance Query

[GPGeorge]

I used Duane's version. My version gave the same amount on each record

So, that would be "Yes, Duane's version raised the error?"

 

[Gasman]

Upload a DB so show the issue.

 

[DHookom]

You could try several troubleshooting steps. I would first open the immediate window (press Ctrl+G) and enter the following with a good account and valid ID.
?Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","[Account] = '[known legitimate Acct]' And ID <= 1000')

If it works, try the query with above expression.
If it works, add a new column to the query
test: [Forms]![frmBankAccounts]![Account]
If you see correct value from the form, consider providing a sample database with just the required objects.

 

[Gasman]

TBH I am surprised that (DSum("Nz(Debit,0)-Nz(Credit,0)", works.

 

[AngelSpeaks]

So, that would be "Yes, Duane's version raised the error?"

Yes

 

[AngelSpeaks]

Upload a DB so show the issue.

Working on it.

 

[AngelSpeaks]

Working on it.

Here's the DB. I converted text Account to AccountID. Thanks. I have two forms to select the checking account. frmBankAccounts is a bound main form with subfrmCheckRegister. frmSelectRegister has a combobox to select checking account and then opens form frmCheckRegister.

 

[arnelgp]

I am using Vba to set the Balance on your Query, qryCheckRegister.
see the Order By of the query.

 

[DHookom]

If you balance is for a specific Account then the query and DSum() must be filtered for the specific Account. I don't believe you mentioned or were clear the balance was for a specific account. Try an expression like:

Balance: Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","AccountID = " & [AccountID] & " AND ID<=" & [ID]))

 

[Gasman]

@DHookom
See post #24.

 

[DHookom]

Sorry, I was looking at the original poster’s comments. I guess this is why it is bad practice to highjack a post.

 

[AngelSpeaks]

If you balance is for a specific Account then the query and DSum() must be filtered for the specific Account. I don't believe you mentioned or were clear the balance was for a specific account. Try an expression like:

Balance: Val(DSum("Nz(Debit,0)-Nz(Credit,0)","qryCheckRegister","AccountID = " & [AccountID] & " AND ID<=" & [ID]))

FrmSelectRegister sends a filter when it opens form frmCheckRegister.

Thanks

 

[AngelSpeaks]

Sorry, I was looking at the original poster’s comments. I guess this is why it is bad practice to highjack a post.

Oops. I see you changed your response.

 

[AngelSpeaks]

I am using Vba to set the Balance on your Query, qryCheckRegister.
see the Order By of the query.

Thanks. I will give it a try.

 

[AngelSpeaks]

I am using Vba to set the Balance on your Query, qryCheckRegister.
see the Order By of the query.

It worker! I did try a function called FNBalance from another discussion but it didn't work because it didn't have the vba code. Thank you so much!