if not isNull with wrong result

[cpampas]

Hello everybody,
i do have the following if then condition:

If Not IsNull(Forms![frmMagicCont]![Procura de Documentos].[Form]![DataM]) Then
      a=1
else
     a=2
end if

in my inmediate window i checked the value of the DataM control, and it returns nothing, i also tested the lenght with :
len(Forms![frmMagicCont]![Procura de Documentos].[Form]![DataM])
and it returns 0

so why, when running the if condition, the code goes to the line a=1
Any toughts about this ?

 

[MajP]

It is possible to have a zero length string. So the better check is
If (Forms![frmMagicCont]![Procura de Documentos].[Form]![DataM] & "") = ""
this handles null and zls
However zls is pretty hard to create, so there may be something else.

 

[cpampas]

I noticed that this behaviour happens after i press a button in the form where the text control is located , and by pressing that button I delete the text in the textbox

Me.DataM = ""

after this is done if i run the if then condition , i get the undesired result, but if i close the form and then open it again then when I run the if then condition i get a=2 ( wich is the correct result)
the control and form are unbound, so I would exclude any requery or refresh issues

 

[Gasman]

I noticed that this behaviour happens after i press a button in the form where the text control is located , and by pressing that button I delete the text in the textbox

Me.DataM = ""

after this is done if i run the if then condition , i get the undesired result, but if i close the form and then open it again then when I run the if then condition i get a=2 ( wich is the correct result)
the control and form are unbound, so I would exclude any requery or refresh issues

So YOU create a ZLS by deleting the text?
Then you open the form afresh, with an unbound control (that will be Null?)

 

[bastanu]

Because you're not "deleting" the data, you set it as a zero length string(ZLS). Try using Me.DataM = Null instead.

Cheers,

 

[MajP]

It is possible to have a zero length string. So the better check is
If (Forms![frmMagicCont]![Procura de Documentos].[Form]![DataM] & "") = ""
this handles null and zls
However zls is pretty hard to create, so there may be something else.

Thanks for proving my point. Creating a ZLS is pretty hard to do, but doing it on purpose is one way. Importing from Excel is another common means.

 

[Pat Hartman]

To summarize:
"" and Null are not the same thing. Depending on what's in your table, you can run a query:

Select somefield, count(*) as RecCount
From YourTable
Group by somefield;

You will see two "empty" lines if the table contains both ZLS and nulls in the same field.

And just to state the obvious, ZLS is a STRING and so it is invalid when applied to a numeric field.

Me.MyDate = "" --- will raise an error
Me.MyDate = Null --- will not raise an error unless you have made MyDate required.

 

[cpampas]

for the kind help thank you all
Me.MyDate = Null, works just great

 

[Pat Hartman]

You're welcome.