Solved

Complicate DSum syntax

Posted on 2014-11-24
6
116 Views
Last Modified: 2014-11-24
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] <> "")

??
0
Comment
Question by:SteveL13
  • 4
  • 2
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40463059
"" 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)
0
 

Author Comment

by:SteveL13
ID: 40463157
Am getting invalid syntax.
0
 

Author Comment

by:SteveL13
ID: 40463170
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.
0
Backup Your Microsoft Windows Server®

Backup 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.

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40463172
Looks like it is missing the final "

...   Is Not Null")
0
 

Author Comment

by:SteveL13
ID: 40463186
Yep.  Thanks.
0
 

Author Closing Comment

by:SteveL13
ID: 40463189
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!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
Restrict list data depending upon user name 3 19
how to link subforms ms/access VBA 6 26
Access 2010 Query Syntax 5 15
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

932 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

9 Experts available now in Live!

Get 1:1 Help Now