Solved

Another syntax issue

Posted on 2014-11-28
5
96 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:
Simon 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:Simon
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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