Avatar of SteveL13
SteveL13
Flag for United States of America asked on

Another syntax issue

What is wrong with:

(DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ="ORD"")
Microsoft Access

Avatar of undefined
Last Comment
SteveL13

8/22/2022 - Mon
Flyster

See if this works:

(DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = '" & "ORD")

Flyster
ASKER CERTIFIED SOLUTION
Simon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SteveL13

ASKER
That one worked.  Thank you!
Simon

You're welcome. I remember struggling when I first built these domain aggregate functions, especially when inserting control values like [Forms]![frmParts]![txtPART_NO] into the formula. I find it helps to temporarily replace them with a hard-coded value because it simplifies the testing and makes the formula easier to examine for balance (open/close brackets, paired single & double quotes).
=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ="ORD"") 
v
=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '55' AND [TRNX_TYPE] ='ORD' ") 

Open in new window

Notice that when you take the reference to the control out you can also take out the surrounding & and " from either side, so it makes quite a difference to the length of the formula.
Also worth spacing out the single and double quotes where possible to improve readability.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SteveL13

ASKER
Simon,

Yes, the single and double quotes really confuse me.  

--Steve