SQL IN Clause and Null Value

[rdemyan via AccessMonster.com]

I have to deal with another database. I import data from that database into
my application.

The other database has holes. There is a key field, Site, that at times is
Null, yet has buildings assigned to it.

On some of my forms I allow the user to select multiple sites from a
multiselect listbox. I then create an In clause for the SQL statement in Code
and then execute the SQL statement in code.

This works except for a Null site. How do I include a Null site in my In
clause. Here's the code I'm currently using to create the In clause. The
variable, ListofSites, is then used in a dynamically generated SQL statement
for execution in code. The question is: How do I add Null to ListofSites so
that it is recognized in the SQL statement as the Null Site. Also, Null
should only be added to ListofSites if it actually exists as a Site. It might
not.

Code:
------------------------------------------------------------------------------
--


Dim i As Integer, ListofSites As String


ListofSites = ""

For i = 0 To Me.Site_ListBox.ListCount - 1

If Me.Site_ListBox.Selected(i) Then

If ListofSites = "" Then
ListofSites = "'" & Me.Site_ListBox.Column(0, i) & "', '"
Else
ListofSites = ListofSites & Me.Site_ListBox.Column(0, i) & "', '"
End If

End If

Next i

If ListofSites <> ""
ListofSites = "(" & left(ListofSites, Len(ListofSites) - 3) & ")"
End If


------------------------------------------------------------------------------
--


So, as an example of what I'm currently able to generate:

ListofSites = ('New York', 'Boston', 'Miami')

If the Null site is currently selected then my code creates the following:

ListofSites = ('New York', '', 'Miami')

Maybe I just need to use Nz on the table field, Site, in the Select portion
of the SQL statement.

So maybe:

WHERE Nz(Site,'') IN " & ListofSites & " .........

Suggestions, thoughts, etc.

Thanks.

 

[Douglas J. Steele]

You can't use Null in an IN clause, since IN implies =, and = doesn't work
with Null.

Your idea of Nz(Site, ''), and including '' in the IN clause is probably the
best.

The only other possibility would be to add OR Site IS NULL at the end when
you needed it.