Invalid Use of Null Error

J

Josef

hi,

i am trying to get data from a record set. some fields are null and when
i try to use this data i get an 'Invalid Use of Null' Error.

----- Code Snippet -----

Set dbs = CurrentDb()
strSQL = "SELECT * FROM tbl_STUDENT;"
Set rst = dbs.OpenRecordSet(strSQL)

While (Not rst.EOF)

stSudentID = rst("Student ID")
stFName = rst("First Name")
stMName = rst("Middle Name")
stLName = rst("Family Name")
stGender = rst("Gender")
stDOB = rst("DOB")

....

rst.MoveNext

WEnd

----------------------

any suggestions on how i can test for Null, or use a different routine
to get the data from the record set.

many thx.
 
T

Terry

Can't pass a null string but you can an empty one.
stMName = IIF(IsNull(rst("Middle Name")),"",rst("Middle Name")
 
G

Graham Mandeno

Hi Josef

You have two options:

1. Make your variables of type Variant. A Variant is the only data type
that can hold a Null value. Only do this if you want to distinguish between
Nulls and empty strings, because Variants are less efficient to process.

2. Use the Nz function to convert Nulls to empty strings:
stGender = Nz ( rst ( "Gender" ), "" )
 
Top