We help IT Professionals succeed at work.

Another syntax issue

SteveL13
SteveL13 asked
on
119 Views
Last Modified: 2014-11-29
What is wrong with:

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

CERTIFIED EXPERT

Commented:
See if this works:

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

Flyster
Principal Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That one worked.  Thank you!
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
Simon,

Yes, the single and double quotes really confuse me.  

--Steve
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.