summing values

[Dave D]

I have this query that works fine ... as a query. However, I need to use it in a report to get a value for a single text box. And, since I need to do this for each month, I can't see making 12 sub-reports just to get a sum. I was trying to use DSUM (example provided) but that's not working - it provides the sum of everything; it doesn't provide the sum for just January.

Suggestions?

SELECT Sum(tblTrackingDelivery.poValue) AS SumOfpoValue, Month([payVendorNLTDate])=1 AS January
FROM tblTrackingDelivery
GROUP BY Month([payVendorNLTDate])=1
HAVING (((Month([payVendorNLTDate])=1)=True));

Me.txtJanPayOut.Value = Nz(DSum("poValue", "tblTrackingDelivery", "Month(payVendorNLTDate) = 1", 0))

[ssanfu]

Looks like a closing parenthesis might be in the wrong place.
You have

Me.txtJanPayOut.Value = Nz(DSum("poValue", "tblTrackingDelivery", "Month(payVendorNLTDate) = 1", 0))

Try

Me.txtJanPayOut = Nz(DSum("poValue", "tblTrackingDelivery", "Month(payVendorNLTDate) = 1"), 0)

Don't need ".Value"..... it is the default property. Doesn't hurt - just extra typing.



Edit......Does "payVendorNLTDate" have a year component?. Might have to add the year to limit the DSum to a specific year.



---------------------------
Without NZ(): DSum("poValue", "tblTrackingDelivery", "Month(payVendorNLTDate) = 1")

Adding NZ(): NZ(DSum("poValue", "tblTrackingDelivery", "Month(payVendorNLTDate) = 1"),0)

[Dave D]

Thank you, ssanfu. Your change worked.