Just what is it about checking for Null !

[Vivitech]

I know this is going to be a silly one, but...!

I've got:

Dim Rs As DAO.RecordSet
Dim sStored As String

...(open record set etc.)

sBatchIDStored = Rs("fld1").Value

but if the field happens to be empty I get a "Invalid use of Null".

So I try to trap this with:


If RsProc(sFld).Value <> Null Then
sBatchIDStored = Rs(sFld).Value
End If

but then even if the field has something in it, the If doesn't catch it and the assignment is skipped.

I also tried

If RsProc(sFld).Value Is Null Then...

but that throws 424 "Object required".

I'm a seasoned C programmer and can't believe I'm having this much trouble with a simple assignment! What am I doing wrong?

 

[ChrisO]

G’day Vivitech.

    [color=green]'   Value is optional.[/color]
    If Not IsNull(RsProc(sFld).Value) Then
        sBatchIDStored = Rs(sFld).Value
    End If

    [color=green]'   Or simply...[/color]
    sBatchIDStored = Nz(RsProc(sFld).Value, "")

Hope that helps.

Regards,
Chris.

 

[Vivitech]

Sure enough - silly it was, but I think I've found it myself:

The trap needs to be

If Not IsNull(Rs("fld1").Value) Then
...



Still a bit frustrating why the compiler lets the other "Ifs" through when they are bound to fail at runtime.

 

[Vivitech]

Thanks ChrisO - now you've reminded me, I've actually used that Nz approach before in a report formula to overcome "invalid use of null".

Cheers! Nice to get some prompt help at midnight from the other side of the world!