Code always fails

  • Thread starter Thread starter Darhl Thomason
  • Start date Start date
D

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

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

or, even better,

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

saves a function call.

</picky>
 
Thanks, Douglas, I like that.

Douglas J Steele said:
<picky>

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

or, even better,

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

saves a function call.

</picky>
Click to expand...
 
Back
Top