how to check for nulls in a recordset

[Tarasque]

im creating a recordset from a table in access,

then going through each field trying to ascertain whether it is empty or not, all of the following have produced errors

if myRS!Field1 is null

varHolder = myRS!Field1
if len varHolder = 0

if myRS!Field1 = ""

Does anyone have any idea how to check if one of the fields in the recordset is empty?

 

[Guest_imported]

If I undersood correctly, you are looking for something like:

recordset.movefirst
while not eof.recordset
if isnull(!field1) then...
your code here
end if
recordset.movenext
wend

Hope it helps,

AmaHerrn

 

[Tarasque]

that is similar to the code I have been using before, the problem is that it is not triggering on the null values.

 

[Tarasque]

Well, ive fixed the problem (incase anyone else has the same errors)

it seems that access has problems checking if the length is = 0 so i just reversed the logic and used the following syntax

varSQL51 = myRS![asset type]
If Len(varSQL51) > 1 Then
varSQL51 = "= '" & varSQL51 & "'"
Else
varSQL51 = "is null"
End If

 

[mac318]

You still have not resolved your primary problem; that is, understanding the null concept. Null does not exist; therefore, the length of Null can not be measured. In VBA lingo, Null contains no properties such as length, width, value. The only method which can be used with Null is to test for its existence.

mac

 

[Tarasque]

the reason that I have used Len as a way to check the length of the variant is because none of the is null operators where working, SO..... the only way I could get anything to work was to convert the recordset value into a variant, (i tried then checking if that variant was null - but that didnt work) so i decided to check if the length was greater than 1 (thereby proving that it was not null)

I know that this is a rather shoddy work around but neither I or another VB programmer could find a way to get null detection to work within recordsets.

 

[mac318]

OK. Did you try this construct?
If IsNull(myRS!Field1) then
perform code
End If

mac