We help IT Professionals succeed at work.

One more DSum syntax issue

SteveL13
SteveL13 asked
on
What is wrong with this line of code?

Me.txtAMDemand = Nz(DSum("Qty", "tblForecastImports", "[PartN] = '" & Me.txtPartN & "' And [Planned] = False" And [WarehouseID] = "AM"), 0)

--Steve
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
Me.txtAMDemand = Nz(DSum("Qty", "tblForecastImports", "[PartN] = '" & Me.txtPartN & "' And [Planned] = False And [WarehouseID] = 'AM'"), 0)

Author

Commented:
Hmmm, the syntax seems to be correct thanks to your recomendation but the result is empty. I'm not even getting a "0" which I would expect from the Nz.  The answer should be 1,000 based on the qty field in tblForecastImports where WarehouseID is "AM".  

The field "Planned" is a Yes/No field in the table.  Could that be a clue?

--Steve
Most Valuable Expert 2012
Top Expert 2013

Commented:
The syntax for Planned (Y/N field) should be okay.

Double-check the value you are using for PartN (also, is that text or numeric)?

Author

Commented:
PartN is text.    The value I'm using is correct.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Give this a try:

Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) & " And Planned = 0 And WarehouseID = 'AM'"),0) 

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
And double-check that all of your field and control names are correct.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Finally - are you positive that the code is actually running?  Set breakpoints to verify.

I agree that you should be seeing 0 if no results are returned from the DSum's criteria.

Author

Commented:
Still not working correctly.
Top Expert 2016

Commented:
post sample records from table "tblForecastImports"
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try adding one criteria at a time and see where things go wrong:

1.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) ),0)  

Open in new window


2.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) & " And Planned = False "),0)  

Open in new window


3.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) & " And Planned = 0 And WarehouseID = 'AM'"),0)  

Open in new window


Also try this query:

SELECT SUM(Qty) as SumOfQTY
FROM tblForecastImports
WHERE PartN = 'Place your actual PartN between these quotes' And Planned = FALSE And WarehouseID = 'AM'

Open in new window


If the value returned from that query is not what you expect, then you need to re-consider the criteria you are applying.

Author

Commented:
Ok.  This is very strange.  I tried all three line of code and got nothing on the report in txtAMDemand.  Not even the first line of code ended in a result.  Then I copy/pasted the SQL query in a test query and DID receive a numeric result.

????
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Have you tried just putting this code in the immediate window, to see whether it is returning what you expect?  Use a partnumber that you are certain exists.

?NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & "10000" & CHR(34) & " And Planned = 0 And WarehouseID = " & chr$(34) & "AM" & chr$(34)),0)

I have a function I call when I want to wrap text in a character.  It accepts a variant which represents the value to be wrapped (allowing you to pass in a NULL value), and an optional character to place on each end of the value.  This allows me to use quotes, single quotes or the # sign as delimiters.  It also replaces single instances of the wrap character that are found within the text to be wrapped, to deal with embedded quotes or apostrophes.
Public Function fnWrap(WrapThis as Variant, Optional WrapWith as String = """") as string

    fnWrap = WrapWith & Replace(NZ(WrapThis, ""), WrapWith, WrapWith & WrapWith) & WrapWith

End function

Open in new window


Using this function, you could rewrite your line as:

?NZ(DSum("Qty", "tblForecastImports", "PartN = " & fnWrap("10000") & " And Planned = 0 And WarehouseID = " & fnWrap("AM")),0)

This is both shorter, and easier to read.

Most Valuable Expert 2012
Top Expert 2013

Commented:
What event are you running the code from?
The open or load events of a report will probably not work - it should be Detail Format or similar.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Given your latest post, I would agree with mbizup that it looks like it may be a problem of what event you are using to fire this code.

What section is the control (Me.txtAMDemand) located in?  You code should most likely be in the Format or Print event of that section, whether it be a group header/footer or detail section.
Most Valuable Expert 2012
Top Expert 2013
Commented:
You can also try placing this directly in the Control Source property of your textbox (include the = sign):

= NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & [txtPartN] & CHR(34) & " And Planned = FALSE And WarehouseID = 'AM'"),0) 

Open in new window

Author

Commented:
I had the code in the on print event.  I put it in the control source of the textbox and IT WORKS!!!

I'm sure glad its working but I sure wonder why it doesn't work in the onprint event.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
OnPrint event of what section?
Most Valuable Expert 2012
Top Expert 2013

Commented:
Did you try the Detail Format event?  I think that might have worked as well.
Most Valuable Expert 2012
Top Expert 2013

Commented:
<Detail Format event?>

(Or the format Event of whatever section your control resides in)

Author

Commented:
Had it in the detail section which is where the textbox is located.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Well,
 I'm glad its resolved.

Regarding WHY we had so much trouble with the code vs the control source - it could have been one of a variety of reasons.

Not all report events fire (that is why I suggested early on to add a break point to see if the code was actually running).

For example, the Load Event only fires in certain views.

Also, I havent tested this recently, but I believe that in Report View in Access 2007, *none* of the section Events fire (ie: Section print and format events simply won't work).