Run Time Error 94

[Guest]

I'm attempting to import data from an access query into an excel spreadsheet.
Everything works great unless one of the fields does not contain data. I
then get an error message Invald use of Null... run time error '94'

This is the code that I'm trying to use to handle that situation but it
never follows the "true" path when the field is null.

If Nz(rst("" & rst2("SchoolCode") & "")) = Null Then
.Range(colname).Value = "-"
Else
.Range(colname).Value = CStr(rst("" &
rst2("SchoolCode") & ""))
End If

Any help would be greatly appreciated.

 

[Guest]

Never mind... I figured it out it was a stupid mistake on my part. I tried
using the IsNull syntax before and it didn't work but I realized my mistake
and corrected it...

If IsNull(rst("" & rst2("SchoolCode") & "")) Then
.Range(colname).Value = "-"
Else
.Range(colname).Value = CStr(rst("" &
rst2("SchoolCode") & ""))
End If

 

[Douglas J Steele]

Ignoring everything else for the moment, If Nz(rst("" & rst2("SchoolCode") &
"")) = Null Then will never be true, for a couple of reasons. The Nz
function will guarantee that rst("" & rst2("SchoolCode") & "") isn't null,
but even if it didn't, you can never check whether anything is = to Null:
you must use the IsNull function.

Strikes me that all you need is

.Range(colname).Value = Nz(CStr(rst("" & rst2("SchoolCode") & "")), "-")

rather than that If-Then-Else construct.