WHERE Condition with NULL

[Jonathan Wood]

I've written some code that looks something like this:

Dim var As Variant

var = DLookup("CustomerID", "Customers",
"FirstName=""" & sFirstName & """ AND "
"LastName=""" & sLastName & """

This seems to work just fine. However, since sFirstName and sLastName are
strings, they will be "" instead of NULL. Therefore, this code fails if
Customers.FirstName or Customers.LastName is NULL.

The only solution I can think of here is rather involved. Does anyone know a
more slick way to have this work for NULL values when my comparison strings
are ""?

Thanks.

Jon

 

[John_G via AccessMonster.com]

Hi -

Use the Nz function with sfirstname and sLastname:

var = DLookup("CustomerID", "Customers",
"FirstName=""" & nz(sFirstName,"''") & """ AND "
"LastName=""" & nz(sLastName,"''") & """

Here, Nz wiill return an empty string if sFirstName or sLastName is Null.

John

 

[Allen Browne]

So you need to look up the first name only if supplied, and the last name
only if supplied, and if both are supplied you need to match both.

The idea is to build up the 3rd argument for DLookup() as a string, based on
whichever values you have. The example below is designed to make it easy to
add more than 2 criteria if needed.

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If sFirstName <> vbNullString Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
End If

If sLastName <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)
Else
var1 = Null
End If

 

[Douglas J. Steele]

<picky>

I've always heard it's better to use

If Len(sFirstName) > 0 Then

than

If sFirstName <> vbNullString Then

</picky>

 

[Jeff Boyce]

?... define "better" ...?

Skippy

<picky>

I've always heard it's better to use

If Len(sFirstName) > 0 Then

than

If sFirstName <> vbNullString Then

</picky>

 

[Jonathan Wood]

Thanks but I don't believe that will work because, as mentioned before, an
empty string compared to a NULL value will return False.

Jon

 

[Jonathan Wood]

Thanks, but this didn't appear to catch NULL values.

Put another way, here's a stab at the "more involved" method I was thinking
about:

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If Len(sFirstName) > 0 Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
Else
strWhere = strWhere & "(FirstName IS NULL) AND "
End If

If sLastName <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
Else
strWhere = strWhere & "(LastName IS NULL) AND "
End If

strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)

My actual code has a few more conditions. I was just wondering if there was
a more concise way to deal with such things.

Thanks.

P.S. My changes assume IS NULL is supported. That's what I use with SQL
Server and I'm guessing Access supports it as well.

Jon

 

[Douglas J. Steele]

What are sFirstName and sLastName? If they're variables, what type of
variable? If, as you implied earlier, they're string variables, they cannot
contain Nulls. Are you sure, though, that they contain a zero-length string,
and not spaces?

Try

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If Len(Trim$(sFirstName)) > 0 Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
Else
strWhere = strWhere & "(FirstName IS NULL) AND "
End If

If Trim$(sLastName) <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
Else
strWhere = strWhere & "(LastName IS NULL) AND "
End If

strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)

If that still doesn't work, try:

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If Len(Trim$(Nz(sFirstName, vbNullString))) > 0 Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
Else
strWhere = strWhere & "(FirstName IS NULL) AND "
End If

If Trim$(Nz(sLastName, vbNullString)) <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
Else
strWhere = strWhere & "(LastName IS NULL) AND "
End If

strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)

 

[Douglas J. Steele]

Due to how strings are stored, apparently it's faster simply to check the
length, rather than compare their value to vbNullString. Of course, the
differences would be infinitessimally small...

 

[Jonathan Wood]

Sorry, in the languages I usually use, the "s" prefix usually indicates a
string--and that's what they are.

As I believe I mentioned in my original post, they would be an empty string
("") if they had no value. That was the problem I was having.

String variables cannot contain NULL.

I did something similar to what you do below in my other post. It just
seemed like there might be a more straight forward way. I guess not.

Thanks.

 

[J_Goddard via AccessMonster.com]

Hi -

For this to work, you would have to ensure that your database did not contain
zero-length strings in FirstName or LastName (use the Allow Zero Length
property).

If you have that set, your code should work. However, there is a potential
problem here - what happens if your database contains two or more customers
named "John Smith"? Or when you are looking for your only "John Smith" but
accidentally leave the first name out of your search?

Expecting to get only one record when you look for FirstName and LastName is
always risky.

John

Thanks, but this didn't appear to catch NULL values.

Put another way, here's a stab at the "more involved" method I was thinking
about:

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If Len(sFirstName) > 0 Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
Else
strWhere = strWhere & "(FirstName IS NULL) AND "
End If

If sLastName <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
Else
strWhere = strWhere & "(LastName IS NULL) AND "
End If

strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)

My actual code has a few more conditions. I was just wondering if there was
a more concise way to deal with such things.

Thanks.

P.S. My changes assume IS NULL is supported. That's what I use with SQL
Server and I'm guessing Access supports it as well.

Jon

So you need to look up the first name only if supplied, and the last name
only if supplied, and if both are supplied you need to match both.

[quoted text clipped - 42 lines]

 

[Jonathan Wood]

Yes, I verified these fields do not allow empty values.

I'm searching for additional fields not included in my original example.
It's not perfect but this is the spec required.

Thanks.

Jon

Hi -

For this to work, you would have to ensure that your database did not
contain
zero-length strings in FirstName or LastName (use the Allow Zero Length
property).

If you have that set, your code should work. However, there is a
potential
problem here - what happens if your database contains two or more
customers
named "John Smith"? Or when you are looking for your only "John Smith"
but
accidentally leave the first name out of your search?

Expecting to get only one record when you look for FirstName and LastName
is
always risky.

John

Thanks, but this didn't appear to catch NULL values.

Put another way, here's a stab at the "more involved" method I was
thinking
about:

Dim var1 As Variant
Dim strWhere As String
Dim lngLen As Long

If Len(sFirstName) > 0 Then
strWhere = strWhere & "(FirstName = """ & sFirstName & """) AND "
Else
strWhere = strWhere & "(FirstName IS NULL) AND "
End If

If sLastName <> vbNullString Then
strWhere = strWhere & "(LastName = """ & sLastName & """) AND "
Else
strWhere = strWhere & "(LastName IS NULL) AND "
End If

strWhere = Left$(strWhere, lngLen)
var1 = DLookup("CustomerID", "Customers", strWhere)

My actual code has a few more conditions. I was just wondering if there
was
a more concise way to deal with such things.

Thanks.

P.S. My changes assume IS NULL is supported. That's what I use with SQL
Server and I'm guessing Access supports it as well.

Jon

So you need to look up the first name only if supplied, and the last
name
only if supplied, and if both are supplied you need to match both.

[quoted text clipped - 42 lines]

Jon

 

[Allen Browne]

Yes, I think I first heard that suggestion from Ken Getz, Doug.
Back in the Access version 2 days.

The idea was to use a numeric operation (length of string) rather than a
string operation, because string comparisons are less efficient than numeric
operation.

The way compiler optimization runs today, I'd be quite surprised if there is
any significant difference.

(You probably think similarly, hence your "picky" tag.)

 

[Allen Browne]

I guess I'm not clear on what you want.

Perhaps you intend this kind of thing:

If Len(sFirstName) > 0 Then
strWhere = strWhere & "((FirstName Is Null) OR (FirstName = """ &
sFirstName & """)) AND "
End If