Invalid Use of NULL

[Stephanie T.]

I've got a query that I have copied from the Northwind database in an effort to understand how they have calculated their subtotals on their order form and order subform. I have changed the names of the fields (obviously) to match the names of my fields.

Here is the SQL of the Query:

SELECT DISTINCTROW [tblSalesDetailSynch].ID, Sum(CCur([CasePrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal
FROM [tblSalesDetailSynch]
GROUP BY [tblSalesDetailSynch].ID;

But I keep getting "Invalid use of null" The CCur function rounds currency values consistently.

Frankly, I don't even know what this means to clean up the problem. I'm on Access 97.

Thank you,
Stephanie

 

[Jon K]

I think there are some blanks (i.e. Null values) in your fields so the CCur() function has failed to convert the Null values to currency.

You can use the Nz() function to convert any Null values to 0.

Try this:-
Sum(CCur(Nz([CasePrice])*Nz([Quantity])*(1-Nz([Discount]))/100)*100) AS Subtotal

 

[Stephanie T.]

Jon,

Thank you that did the trick! Now if I could just get the other equations to work I would be a happy camper!

Best to you,
Stephanie