Solved

Very complicated syntax

Posted on 2014-11-28
8
113 Views
Last Modified: 2014-11-28
Can someone help me with this syntax?

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] in ('I','O')") + (DSum("QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND IsNull([TRNX_TYPE]) - DSum("QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND IsNotNull([DATE_RECD]))

I probably have a lot wrong with it.
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40470579
Try this:

=DSum("[QTY_ORDERED]","tblInventory",
"[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] in ('I','O')") +
(DSum("QTY_ORDERED]","tblInventory",
"[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] Is Null") -
DSum("QTY_ORDERED]","tblInventory",
"[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [DATE_RECD] Is Not Null")

/gustav
0
 

Author Comment

by:SteveL13
ID: 40470591
Am getting a #Error in the field on the form.

But when I copy/pasted your suggestion into the control source it didn't complain except that it wanted a ) at the end.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40470609
Oh, sorry, but it's the other way round (line 3):

=DSum("[QTY_ORDERED]","tblInventory",
 "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] in ('I','O')") +
DSum("QTY_ORDERED]","tblInventory",
 "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] Is Null") -
DSum("QTY_ORDERED]","tblInventory",
 "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [DATE_RECD] Is Not Null")

/gustav
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:SteveL13
ID: 40470633
Hmmm,

I tried picking this apart in sections.  When I cop/pasted the first part it gave me a result:

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

But when I tried the 2nd part by itself:

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

I get #Error in the field.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40470642
You should correct the typo:

=DSum("[QTY_ORDERED]"

also for the third part.

/gustav
0
 

Author Closing Comment

by:SteveL13
ID: 40470693
Perfect!
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40470695
Also, you may need to prevent Nulls:

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

for all three parts.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40470748
Ok.  Will do.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…

717 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