Syntax Error

SteveL13
SteveL13 used Ask the Experts™
on
I'm getting a syntax error on:

    Me.txtQtyOnOrder = Nz(DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ='ORD'"), 0) - Nz(DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = "I"), 0)

Can someone help?  I think it is at the end of the code line.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this. Replace the last condition with this.
[TRNX_TYPE] = 'I'"), 0)

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It should probably read:
Me.txtQtyOnOrder =
Nz(DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ='ORD'"), 0) -
Nz(DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = 'I'"), 0)

/gustav
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Dim strCriteria1 as string, strCriteria2 as string

strCriteria1 =  "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] ='ORD'"
debug.print strCriteria1
strCriteria2 = "[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = 'I'"
debug.print strCriteria2
Me.txtQtyOnOrder = Nz(DSum("[QTY_ORDERED]", "tblInventory", strCriteria1), 0) _
                                   -  Nz(DSum("[QTY_ORDERED]", "tblInventory", strCriteria2), 0)

The problem is that you tried to wrap the "I" at the end of the 2nd DSUM with quotes.  I prefer to break this process up into steps, so I can identify the problems easily.
Top Expert 2016
Commented:
try changing this part

[TRNX_TYPE] = "I"), 0)

with

[TRNX_TYPE] = 'I'"), 0)
The last condition is closing the main quotes. Use single quotes around it.
" AND [TRN_TYPE] = 'I'"), 0)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial