Code always fails

[Darhl Thomason]

I have this code snippet in my db. Every time this code runs, it always
executes the code under the Else clause, even when strFilter = Null
(verified by adding a watch in the VBA editor). What am I missing here?

Thanks!

Darhl

If strFilter = Null Or strFilter = Empty Or strFilter = "" Then
CurrentDb.Execute strSQLMakeTable & strSQLMakeTable2, dbFailOnError
Else
strSQLMakeTable2 = strSQLMakeTable2 & " WHERE "
CurrentDb.Execute strSQLMakeTable & strSQLMakeTable2 & strFilter,
dbFailOnError
End If

 

[Sandy H]

Hi Darhl
Try the following:

if IsNull(strFilter) or strFilter = "" then
........

Sandy

 

[Allen Browne]

Darhl, as Sandy points out, testing for something equal to null will always
fail. For an explanation of why, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
It's error #5 in that article.

But there is another issue here too. Since the name of your variable is
"strFilter", I assume that you have declared this variable as a String type,
so you will have a line like this at the top of your procedure:
Dim strFilter As String
If so, a string variable cannot be null, so the problem you are experiencing
might be further up in your procedure, above the code you posted.

 

[Darhl Thomason]

Thanks Allen and Sandy.

Allen, I'll read your article on Null, but strFilter is declared as Variant,
not as String. I was playing with the code last night and thought "Hmm,
that should probably be a string not a variant" but when I changed it to
string, it broke my code. I do check it for null in multiple places.

Darhl

 

[Klatuu]

If you are expecting a variable may ever be assigned Null, you need to
declare it as Variant. It is the only variable data type you can assign a
Null value without an error. If you need to convert it back to a string
after you have checked for null, you could use th Cstr function.

One other trick. Here is a line of code I use when I need to check for both
Null and Zero Length strings:

If Len(Trim(Nz(VarSomeThing,""))) = 0 Then

The Nz will convert Null or Zero to a Zero Length String
The Trim will remove all leading and trailing spaces.

 

[Douglas J Steele]

<picky>

If Len(Trim(VarSomeThing & "")) = 0 Then

or, even better,

If Len(Trim(VarSomeThing & vbNullString)) = 0 Then

saves a function call.

</picky>

 

[Klatuu]

Thanks, Douglas, I like that.

<picky>

If Len(Trim(VarSomeThing & "")) = 0 Then

or, even better,

If Len(Trim(VarSomeThing & vbNullString)) = 0 Then

saves a function call.

</picky>