Using AND with IIF in a query

[David Ball]

Hi
Using AND with IIF in a query
The IIF statement below works in most cases except where there is no value for the first condition, [ARRIVAL CURRENT ACTUAL] Is Not Null, and there is a value for the second, [ACTUALSTARTSET] Is Not Null. When there is a value for ACTUALSTARTSET and no value for ARRIVAL CURRENT ACTUAL it returns “Yes”, when I thought it should return “No” unless there are values for both.

TESTIf: IIf(([ARRIVAL CURRENT ACTUAL] Is Not Null And [ACTUALSTARTSET] Is Not Null),"Yes","No")
How can I rewrite this to work?
Thanks very much

Dave

 

[plog]

My guess is there are values for those fields, but you think there aren't.

Add these two fields to your query:

Field1Null: IsNull([ARRIVAL CURRENT ACTUAL])
Field2Null: IsNull([ACTUALSTARTSET])

As well as the two fields themselves. Find a record that says Yes, but looks like there is no data in the actual fields. See if the corresponding FieldXNull field is TRUE.

My gut tells me there is an empty string in there (""). An empty string is not null, but appears null.

 

[David Ball]

Thanks very much

Dave

 

[jdraw]

I think plog is onto the issue with his zero length string idea.

However, when you use NOT with AND and OR you may be the victim of deMorgans Laws.

[COLOR="Purple"][I]"not (A and B)" is the same as "(not A) or (not B)"
also,
"not (A or B)" is the same as "(not A) and (not B)"[/I][/COLOR]

Here is a small test I did using variants that I could ensure were NULL. My set up was with a and b to simulate your IIF statement. I used NULL and 8 for a values and NULL and 2 for b values.

The IIF in the second Debug.print is an answer to an interpretation of the poster's question.

Sub TestIsNull()

Dim a As Variant, b As Variant
a = NULL ' substitute 8
b = NULL  'substitute 2
Debug.Print IIf(IsNull(a), "NULL", a) & " a&b  " & IIf(IsNull(b), "NULL", b)
Debug.Print IIf(Not ((IsNull([a])) Or (IsNull([b]))), "Yes", "No") & vbCrLf & "_____________________"  'simulates users

End Sub

Results:
NULL a&b 2
No
_____________________
NULL a&b NULL
No
_____________________
8 a&b NULL
No
_____________________
8 a&b 2
Yes
_____________________

This produces Yes only when a is valued and b is valued
It Produces No when a is null and b is valued
It Produces No when a is valued and b is null
It Produces No when a is null and b is null

 

[arnelgp]

TESTIf: IIf(( "" & [ARRIVAL CURRENT ACTUAL] & [ACTUALSTARTSET] <> ""),"Yes","No")