SteveL13
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
Me.txtAMDemand = Nz(DSum("Qty", "tblForecastImports", "[PartN] = '" & Me.txtPartN & "' And [Planned] = False" And [WarehouseID] = "AM"), 0)
--Steve
Me.txtAMDemand = Nz(DSum("Qty", "tblForecastImports", "[PartN] = '" & Me.txtPartN & "' And [Planned] = False And [WarehouseID] = 'AM'"), 0)
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 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)?
Double-check the value you are using for PartN (also, is that text or numeric)?
ASKER
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)
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.
I agree that you should be seeing 0 if no results are returned from the DSum's criteria.
ASKER
Still not working correctly.
post sample records from table "tblForecastImports"
Try adding one criteria at a time and see where things go wrong:
1.
2.
3.
Also try this query:
If the value returned from that query is not what you expect, then you need to re-consider the criteria you are applying.
1.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) ),0)
2.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) & " And Planned = False "),0)
3.
Me.txtAMDemand = NZ(DSum("Qty", "tblForecastImports", "PartN = " & CHR(34) & Me.txtPartN & CHR(34) & " And Planned = 0 And WarehouseID = 'AM'"),0)
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'
If the value returned from that query is not what you expect, then you need to re-consider the criteria you are applying.
ASKER
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.
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.
?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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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)
(Or the format Event of whatever section your control resides in)
ASKER
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).
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).