Illegal use of Null

[rlusk49]

I am trying to retrieve two fields from a DB based on a person's ID and I have created an array to hold the data. I have provided error handling in case the array is empty and it works OK when there is data in the fields. However, when the fields are empty, I get an error message on the illegal use of null. Any ideas?

 

[johnwm]

In one of my standard .bas modules I have the following function:
Function N2S(X As Variant) As String
If IsNull(X) Then
N2S = ""
Else
N2S = CStr(X)
End If
End Function

Then when I get stuff from a databaseI use:

myStr = N2S(rst.fields(1)) Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[LlomaxX]

Also, here's a neat trick...Just use Format.

Using johnwm's example:

myStr = Format(rst.fields(1))

Format will return an empty string for Null values.

In case you haven't guessed, the original error is probably because you are trying to assign a Null value to a String (or member of a String array). Only Variants can hold a Null value.

 

[docjohn52]

You can get this type of error from the database as well
in the record design "Allow Zero Length" = YES

John

 

[softwareguy]

Or just cover any chance of a null coming back from the data by tacking a vbNullstring on every item you add to the array. That way nothing will ever be null & it won't hurt non null values. It's generally better to use the constant instead of "".