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

asked on

One more DSum syntax issue

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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

Avatar of SteveL13

ASKER

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
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)?
PartN is text.    The value I'm using is correct.
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

And double-check that all of your field and control names are correct.
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.
Still not working correctly.
post sample records from table "tblForecastImports"
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.
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.

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

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan 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
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.
OnPrint event of what section?
Did you try the Detail Format event?  I think that might have worked as well.
<Detail Format event?>

(Or the format Event of whatever section your control resides in)
Had it in the detail section which is where the textbox is located.
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).