I have a query that returns sale transaction details of customers who have not yet paid their bill. I run it once a month and until this month, it's worked fine. I have changed nothing in the database, which is customized from the Product Orders database sample at http://office.microsoft.com/en-ca/te...CT102144001033
but today this query is giving me an "Invalid use of Null" error. The SQL is as follows:
SELECT DISTINCTROW Customers.CompanyName, Orders.OrderDate, Orders.ShipDate, Orders.PostageCharge, Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total Sales], Orders.OrderID, ([Total Sales]*[SalesTaxRate]) AS PST, Round(([Total Sales]+[PST]),2) AS [Invoiced Amount]
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID
GROUP BY Customers.CompanyName, Orders.OrderDate, Orders.ShipDate, Orders.PostageCharge, Orders.OrderID, Orders.SalesTaxRate, Payments.OrderID
HAVING (((Payments.OrderID) Is Null));
Now I know that the HAVING clause doesn't look right. HAVING... is null is improper SQL, as far as I can tell, but I don't know what to change to make it work. I can't just drop the HAVING clause. I don't understand why the query used to work but no longer does, given I've made no changes to it. Could a data value entered into a recently added record cause this to happen? What sorts of things might I look for?
Thanks for your reply George. I tried your suggestion, but unfortunately it still gave me the same error. I don't know why Access doesn't like the null operator in this instance. Just for fun I tried using a blank string (Payments.OrderID= "" Payments.OrderID='') in place of the null expression, and it gave me a type mismatch, so obviously null should be the appropriate test. So I'm still hoping to resolve this somehow...
I would open the table(s) that contain these fields: Quantity, UnitPrice, Discount. The click in each column separatly, and do a sort A->Z to see if there are any nulls in any of these columns. Or, you can just put each one of these fields into the Nz() function, like: Nz(Quantity), etc. I think that will take care of it.
Thanks for your efforts George and GolferGuy. I didn't have any luck with the nz function, though maybe I was applying it incorrectly. Instead I made a copy of the database, and, starting with the most recent customer record, I began deleting them one-at-a-time, pausing after each one to run my query. Sure enough, after about 5 deletions, the query ran without incident. Turns out there was one customer record that had been duplicated--once with an attached order, and once without. Back in the original database, I deleted the customer record with no matching order, and everything worked fine. This is probably something I should fix in the database, though, so that it doesn't happen again. But the problem originated with the original sample database, so I'll blame Microsoft
Nor does it (or to be more accurate, JET) market itself to be. But it handles nulls just fine - the fact that it is a file is relevent almost exclusively to academic debates. There is no difference in the way you would treat nulls when using Access than when using an enterprise level product. The "no nulls" debate is more a design philosophy than a technical, proprietry thing.