Solved DSUM with multiple criteria

[brunomesquita]

Hello everyone.

I'm having problems with a balance query I started. So far, I got it working with two criteria. My problem is when I add my third one [TrainingRequestStatus]="Approved".

Balance: Val(DSum(Nz([AmountCredit],0)-Nz([AmountDebit],0),"qryTransactionsExtended","[EmployeeID]=" & [tblTransactions].[EmployeeID] & "AND [TransactionID]<=" & [TransactionID] & "AND [TrainingRequestStatus]='Approved'"))

What is wrong with my formula?

Help, please.

 

[plog]

Are you sure it works with 2? You need to put a space before each AND

 

[arnelgp]

you also need to add quote to the first argument of Dsum():

Balance: Val(DSum("Nz([AmountCredit],0)-Nz([AmountDebit],0)","qryTransactionsExtended","[EmployeeID]=" & [tblTransactions].[EmployeeID] & " AND [TransactionID]<=" & [TransactionID] & " AND [TrainingRequestStatus]='Approved'")) & "")

 

[brunomesquita]

Are you sure it works with 2? You need to put a space before each AND

Balance: Val(DSum("nz( [AmountCredit] ,0)-nz( [AmountDebit] ,0)","qryTransactionsExtended","EmployeeID =" & [tblTransactions].[EmployeeID] & "and [TransactionID]<=" & [TransactionID]))

Yes, this formula is working fine, I don't know how to add the third criteria.

 

[brunomesquita]

you also need to add quote to the first argument of Dsum():

Balance: Val(DSum("Nz([AmountCredit],0)-Nz([AmountDebit],0)","qryTransactionsExtended","[EmployeeID]=" & [tblTransactions].[EmployeeID] & " AND [TransactionID]<=" & [TransactionID] & " AND [TrainingRequestStatus]='Approved'")) & "")

Hey @arnelgp, thanks for the reply.

I did wrap it up with quotes, but I still got the same error. At the end of your formula, it looks like you have one-too-many parenthesis.

Cheers,

Bruno.

 

[brunomesquita]

Guys, I figured it out... @arnelgp put me in the right direction. Here is the working formula:

BalanceApproved: Val(DSum("Nz([AmountCredit],0)-Nz([AmountDebit],0)","qryTransactionsExtended","[EmployeeID]=" & [tblTransactions].[EmployeeID] & " AND [TransactionID]<=" & [TransactionID] & " AND [TrainingRequestStatus]='Approved'"))

Cheers,

Bruno