IS Null or ="" 1

[CathieB]

I am using Access 2002 sp 2 on a Windows 2000 sp 2 computer.

When writing a query or code and a field is text or a string, what is the difference with an if statement when asked as follows, because I get different answers with each statement, and don't understand why.

Iif([field]="", then do something
Iif([field]Is Null then do something

The field is used for a street address, and for some the is null works and for others it does not, and the ="" works. And I can't for the life of me figure out why.

Thanks to anyone for any help you can give.

 

[Spike1950]

Hi CathieB,

Wen using Is Null in an expression it should read
IsNull() - the field comes between the parentheses, like:

IIf(IsNull([field]);"True statement";"Untrue statement&quot

Spike1950

 

[Maquis]

Here's the way I understand it works... (And, any experts out there feel free to correct me if I'm wrong)

When a field is first created and no value is assigned to it, then it's value is Null. Null does not equal "". Null doesn't equal anything which is why you can't say If X = Null then... because nothing will ever equal Null. You have to say If IsNull(X) then...

However, if you have a text field and it does contain a value, but later you delete that value by setting the field = &quot;&quot;, or by hitting <delete> in the field, then it is equal to the empty string value (&quot;&quot.

To get around the issue, I've started using an IsNothing function and I just call it everytime I want to check a field for Null or 0 or &quot;&quot;. Just paste this code into a utility module in your databases.

Function IsNothing(v As Variant) As Integer
'----------------------------------------------------
' Returns TRUE if the value passed in is Empty, Null or a zero
' length string.
'----------------------------------------------------
IsNothing = False
Select Case VarType(v)
Case V_EMPTY
IsNothing = True

Case V_NULL
IsNothing = True

Case V_STRING
If Len(v) = 0 Then
IsNothing = True
End If

Case Else
IsNothing = False
End Select
End Function



Maq
<insert witty signature here>

 

[JeremyNYC]

Maquis is right on target with ?his? explanation. I'd only extend it one bit by saying that Null basically means unknown. If you think of it that way, it may help keep the two distinct in your mind.

Also, instead of a custom function, you can use the NZ function
nz([something that is null],&quot;&quot = &quot;&quot;
nz([something that is null],0) = 0

Hope that helps.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[CathieB]

Thank You both for your help!