Solved

Another syntax issue

Posted on 2014-11-28
5
92 Views
Last Modified: 2014-11-29
What is wrong with:

(DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ="ORD"")
0
Comment
Question by:SteveL13
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40471071
See if this works:

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

Flyster
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
ID: 40471073
The initial "(" before DSUM and I think you want single quotes around string value 'ORD'

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

Author Closing Comment

by:SteveL13
ID: 40471088
That one worked.  Thank you!
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40471105
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.
0
 

Author Comment

by:SteveL13
ID: 40471606
Simon,

Yes, the single and double quotes really confuse me.  

--Steve
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now