Error when assigning Null fields to variables.

[Guest]

I've been hitting an error when trying to assign a Null field to a variable.
I have set up a record (RecSet) and am looping through it using the
following. As you can see, I tried using the If statement to avoid assigning
the Null value to the variable.

While Not RecSet.EOF
If RecSet!Salutation = Null Then
Salutation= ""
Else
Salutation= RecSet!Salutation
End If
Wend

My problem is that whether the Salutation on the RecordSet is Null or not,
it just goes straight to the Else statement and this produces an error.

Anybody know if there is a way around this other that making sure the field
isn't Null?

Ta.

 

[Guest]

Nothing is = Null
You have to use the IsNull function to determine whether a field, control or
variable contains null.
You can only assign a Null value to a Variant data type. Attempting to
assign a Null value to any other data type variable will cause an error.
This is not true of form controls. They can be assigned a Null value.
There is also a function that prevents this problem. It is the Nz function.
You can find details in VBA Help.
Here is how it would be used in this case.

RecSet!Salutation = Nz(Salutation, "")

Unless there is code in the While ... Wend you did not post, it will create
a endless loop.

 

[Stefan Hoffmann]

hi,

While Not RecSet.EOF
If RecSet!Salutation = Null Then
Salutation= ""
Else
Salutation= RecSet!Salutation
End If
Wend

Use

Salution = Nz(RecSet![Salutation], "")

instead of the If-statement, otherwise use

If IsNull(RecSet![Salutation]) Then
else
End If

Anybody know if there is a way around this other that making sure the field
isn't Null?

You have to use the IsNull() function to test wether a value is Null or
not. Null is not an ordenary value therefore you can't test for identity
using the = operator.


mfG
--> stefan <--

 

[Guest]

Hi, Thanks for the help. The ISNULL function did the job. Oh and yeah, I
forgot to post the line about RecSet.MoveNext which kept it out of perpetual
looping.

Ta.