Solved Help with DSUM criteria - MS ACCESS 2016 (1 Viewer)

RickHunter84

Registered User.
Local time
Today, 15:59
Joined
Dec 28, 2019
Messages
85
Hello friends,

I need a hand with a DSUM calculation I'm trying to do, here is the test code:

Code:
Me.qaTest.Value = Nz(DSum("[QtySold]", "tblSalesOrderHistory", "[SL_FK]=" & Form!SL.Value & " and [TransactionType]='Sale'" & " or [TransactionType]='Exchange'"), 0)

I'm trying to add [QtySold] only where the [SL] matches in the tblSalesOrderHistory and either the word "Sale" or "Exchange" are present, if the criteria finds something else that doesn't matched then I don't want it to add.

Please let know how would you change the syntax, the result I get doesn't match the expected.

Thank you in advance.

Rick
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:59
Joined
Aug 30, 2003
Messages
36,118
When you mix And & Or, you need to specify the desired logic with parentheses:

(A And B) Or C

A And (B Or C)
 

RickHunter84

Registered User.
Local time
Today, 15:59
Joined
Dec 28, 2019
Messages
85
hello,

thank you for the response.

I tried the parenthesis as recommended and still doesn't do the proper calculation, I tried:

Code:
Me.qaTest.Value = Nz(DSum("[QtySold]", "tblSalesOrderHistory", ("[SL_FK]=" & Form!SL.Value & " and [TransactionType]='Sale'" &) " or [TransactionType]='Exchange'"), 0)

when I removed the additional criteria, the calculation is good but I'm trying to filter a layer deeper in case there are additional types on transactions in the future, for example: 'Late fee'

thank you in advance.

Rick
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:59
Joined
Aug 30, 2003
Messages
36,118
Your parentheses are outside the quotes. Try

"([SL_FK]=" & Form!SL.Value & " and [TransactionType]='Sale') or [TransactionType]='Exchange'")

though based on your description it sounds like this is what you want:

"[SL_FK]=" & Form!SL.Value & " and (TransactionType]='Sale' or [TransactionType]='Exchange')")

There is no need for the concatenation when there's no value being interpreted. It just confuses things.
 

RickHunter84

Registered User.
Local time
Today, 15:59
Joined
Dec 28, 2019
Messages
85
Your parentheses are outside the quotes. Try

"([SL_FK]=" & Form!SL.Value & " and [TransactionType]='Sale') or [TransactionType]='Exchange'")

though based on your description it sounds like this is what you want:

"[SL_FK]=" & Form!SL.Value & " and (TransactionType]='Sale' or [TransactionType]='Exchange')")

There is no need for the concatenation when there's no value being interpreted. It just confuses things.
Thank you Paul! Now it works the way I need.

Have a nice night!

Rick
 
Last edited:

Users who are viewing this thread

Top Bottom