Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Complicate DSum syntax

What is wrong 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 [DATE_RECD] <> "")

??
Avatar of PatHartman
PatHartman
Flag of United States of America image
"" is a Zero Length String (ZLS) which is not the same as null.  Since ZLS can only occur in a string data type, it is unlikely that your date received is a ZLS.  It is almost certainly null if it is "empty".
 =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 [DATE_RECD] Is Not Null)
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

Am getting invalid syntax.
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

The first part works ok.  But the 2nd part:

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

If I paste it without the 1st part gives me a syntaz error.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

Yep.  Thanks.
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

Final is:

=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 [DATE_RECD] Is Not Null")


Thank You!