Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Question Unanswered: Invalid use of Null - all of a sudden

    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 any suggestions anyone might have.

    J

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change
    Code:
    HAVING (((Payments.OrderID) Is Null));
    To
    Code:
    HAVING IsNull(Payments.OrderID)
    Include all extra brackets as needed.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    3

    No luck

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

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd just come back to suggest the Nz function

    Access is not a real dB. It doesn't really use nulls, so blank strings all the way, baby!
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2007
    Posts
    3

    solved!

    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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jtpk
    HAVING... is null is improper SQL, as far as I can tell, but I don't know what to change to make it work.
    No - that is perfectly proper SQL.
    Quote Originally Posted by georgev
    Change
    Code:
    HAVING (((Payments.OrderID) Is Null));
    To
    Code:
    HAVING IsNull(Payments.OrderID)
    Include all extra brackets as needed.
    Never use IsNull in access SQL - especially as a WHERE\ HAVING predicate. Seriously - it performs dreadfully. Stick with the ANSI IS NULL.

    Quote Originally Posted by georgev
    Access is not a real dB. It doesn't really use nulls, so blank strings all the way, baby!
    Dude -what are you talking about now?

    Quote Originally Posted by jtpk
    ....everything worked fine.
    Ah crap - didn't make it this far before I started posting. My expectation was that it was a problem in the select clause. I don't expect it to have been a data problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Access isn't a real database, it's a file...
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Access isn't a real database, it's a file...
    It isn't a trdbms - but what is?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    TRDBMS = Truly Relational Database Management System?

    Access is nothing like SS, MySQL, Oracle, etc etc.
    It's a file that appears to be a database (hence it's limitations).

    *shrug*
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    *shrug*
    Poots 1 - George 0
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - I ain't keeping score. Sorry but I can never let a "no nulls please - we're British" statement pass without comment.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha!
    I don't like the nulls vs blank strings arguement, nobody ever wins.

    I'm one of those people who (tries to!) code to standards (XHTML rather than HTML for example) so I wish that someone would solve this once and for all so I can just stick to one or the other
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - nulls and the handling of nulls are defined in the ANSI SQL standard. That good enough for ya?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •