Puzzling "isNull" failing

[Raoul Watson]

I have used isNull statement for as long as I have used VB..

Recently I am devugging a program and it is very clear that the "IsNull"
function sometimes would return a true even when the value is not Null. Any
wild shot or theory as to why this is happening is appreciated. Also, any
other alternative is appreciated since I am stuck and cannot proceed without
fixing this issue (tried = Null and that doesn't work either).

Basically, I have a field in a databsse which could be null, so in the code
i have:

' mfld(x) would have field names like "NAME" etc.

If IsNull(datPrima ryRS.Recordset( mfld(x))) Then
fvalue = ""
Else
fvalue = datPrimaryRS.Re cordset(mfld(x) )
End If

I have verified by stepping that isNull is true while moving the cursor
shows
"Steven Moss" in datPrimaryRS.Re cordset(mfld(x) )

What gives?

[Ralph]

Re: Puzzling "isNull&qu ot; failing


"Raoul Watson" <WatsonR@Intell igenCIA.com> wrote in message
news:DTY%f.715$ UK5.619@trndny0 1...[color=blue]
> I have used isNull statement for as long as I have used VB..
>
> Recently I am devugging a program and it is very clear that the "IsNull"
> function sometimes would return a true even when the value is not Null.[/color]
Any[color=blue]
> wild shot or theory as to why this is happening is appreciated. Also, any
> other alternative is appreciated since I am stuck and cannot proceed[/color]
without[color=blue]
> fixing this issue (tried = Null and that doesn't work either).
>
> Basically, I have a field in a databsse which could be null, so in the[/color]
code[color=blue]
> i have:
>
> ' mfld(x) would have field names like "NAME" etc.
>
> If IsNull(datPrima ryRS.Recordset( mfld(x))) Then
> fvalue = ""
> Else
> fvalue = datPrimaryRS.Re cordset(mfld(x) )
> End If
>
> I have verified by stepping that isNull is true while moving the cursor
> shows
> "Steven Moss" in datPrimaryRS.Re cordset(mfld(x) )
>
> What gives?
>[/color]

That is strange.

The situation where IsNull fails with 'Empty' is well-documented. (ie, some
databases will reset a field (text, nullable) that once had a value back to
Empty or " ", and not Null when the value is deleted), and other issues.

But I have never encountered the situation were a value existed and IsNull
failed. However, the Field.Values in a Recordset collection are Variants and
occasionally a strange dance (or joint psychosis) can develop based on the
Field data type attributes, the SQL used to fetch the data, and whether you
have previous dereferenced the value in your code.

You might go back and see if you can find something unusual about this
particular Field and its history.

There is simple workaround in this case. Use this instead...
fvalue = datPrimaryRS.Re cordset(mfld(x) ) & ""

hth
-ralph

[Raoul Watson]

Re: Puzzling &quot;isNull&qu ot; failing


"Ralph" <nt_consulting6 4@yahoo.com> wrote in message
news:1umdnVVHSK uLYN3ZnZ2dnUVZ_ uidnZ2d@arkansa s.net...[color=blue]
>
> "Raoul Watson" <WatsonR@Intell igenCIA.com> wrote in message
> news:DTY%f.715$ UK5.619@trndny0 1...[color=green]
> > I have used isNull statement for as long as I have used VB..
> >
> > Recently I am devugging a program and it is very clear that the "IsNull"
> > function sometimes would return a true even when the value is not Null.[/color]
> Any[color=green]
> > wild shot or theory as to why this is happening is appreciated. Also,[/color][/color]
any[color=blue][color=green]
> > other alternative is appreciated since I am stuck and cannot proceed[/color]
> without[color=green]
> > fixing this issue (tried = Null and that doesn't work either).
> >
> > Basically, I have a field in a databsse which could be null, so in the[/color]
> code[color=green]
> > i have:
> >
> > ' mfld(x) would have field names like "NAME" etc.
> >
> > If IsNull(datPrima ryRS.Recordset( mfld(x))) Then
> > fvalue = ""
> > Else
> > fvalue = datPrimaryRS.Re cordset(mfld(x) )
> > End If
> >
> > I have verified by stepping that isNull is true while moving the cursor
> > shows
> > "Steven Moss" in datPrimaryRS.Re cordset(mfld(x) )
> >
> > What gives?
> >[/color]
>
> That is strange.
>
> The situation where IsNull fails with 'Empty' is well-documented. (ie,[/color]
some[color=blue]
> databases will reset a field (text, nullable) that once had a value back[/color]
to[color=blue]
> Empty or " ", and not Null when the value is deleted), and other issues.
>
> But I have never encountered the situation were a value existed and IsNull
> failed. However, the Field.Values in a Recordset collection are Variants[/color]
and[color=blue]
> occasionally a strange dance (or joint psychosis) can develop based on the
> Field data type attributes, the SQL used to fetch the data, and whether[/color]
you[color=blue]
> have previous dereferenced the value in your code.
>
> You might go back and see if you can find something unusual about this
> particular Field and its history.
>
> There is simple workaround in this case. Use this instead...
> fvalue = datPrimaryRS.Re cordset(mfld(x) ) & ""
>
> hth
> -ralph[/color]

Ralph..

You have no idea how valuable your golden advice is!!

Obviously the test for null is needed since we will get an
invalid use of null when we try to assign it to a variable/

Your method however, not only works, but it is a much more
efficient way of coding the isNull test..

Thanks again bud..