check for null value in MS Access SQL statement

Question

The following query is not returning values for CurrentVisitor in my ms access 2010 database:

SELECT h.ClientNumber, IIf(h.CheckoutDate=null,"Yes","") AS CurrentVisitor 
FROM VisitsTable AS h 
INNER JOIN (
    SELECT ClientNumber, MAX(LastVisitDate) AS LastVisitStart 
    FROM VisitsTable 
    GROUP BY ClientNumber)  
    AS t 
ON (h.LastVisitStart = t.LastVisitStart) AND (h.ClientNumber = t.ClientNumber);

I think the reason is that the check for null in the If() operation is not written correctly. Can anyone show me how to fix this?

Chris Rolliston · Accepted Answer · 2013-10-15 20:46:44Z

28

Use

Is Null

rather than

= Null

answered Oct 15, 2013 at 20:46

Chris Rolliston

4,8181 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CodeMed Over a year ago

+1. And credit for the answer. Thank you for the quick and correct response.

Gord Thompson Over a year ago

IIf(IsNull(...), ..., ...) would have been the more traditional way to do it, but if Is Null works then who am I to quibble?

Chris Rolliston Over a year ago

@GordThompson - why do you say 'more traditional'? Unless things have changed, 'Something Is Null' is pure SQL, 'IsNull(Something)' (in a JET context) VBA, and it's always better to use pure SQL if you can.

Gord Thompson Over a year ago

IIf() is already fairly specific to Access SQL and VB* (compare with CASE ... WHEN ... in T-SQL) so IsNull() is how old timers like me would tend to write it. But like I said, "If it works..." (and I've already upvoted your answer).

Chris Rolliston Over a year ago

@GordThompson - sure, we're dealing with Access SQL, but that still doesn't impact my preference for Is Null over IsNull (that's why I put 'in a JET context'). Moreover, the Access SQL IIf is different from the Access VBA IIf, as I expect you know (the SQL one short circuits, the VBA one - rather annoyingly - doesn't).

Collectives™ on Stack Overflow

check for null value in MS Access SQL statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related