Invalid Use of Null Error

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

 

[Marshall Barton]

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")

It looks like you're assigning the recordset field values to
string variables, which can not accept a Null Value

If they were Variant, then you could assign Null values to
them.

 

[Terry]

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

 

[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" ), "" )