iif Function in Query

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

I am using the IIF function to show the data in one of two fields.

Exp1: iif([FLD1]=null,[FLD2],[FLD1])

What I want to happen, is to see the data found in FLD2 if FLD1 is blank,
else it will show data in FLD1.

Note: FLD1 is a Date/Time and FLD2 is a Text fld.

What I get with the above formula; It shows only the FLD1 Data.

What am I doing wrong?

Thanks
Matt
 
You can never get a result from comparing Null to any value. Try
Exp1: Nz([FLD1],[FLD2])
 
Duane Hookom said:
You can never get a result from comparing Null to any value. Try
Exp1: Nz([FLD1],[FLD2])
Click to expand...

Would

Exp1: IIf(IsNull([FLD1]), [FLD2], [FLD1])

Do it?

Tom Lake
 
It should have taken you about 10 seconds to test this (if you had Access
available).

Either method should work if FLD1 is actually null and not an empty string.

--
Duane Hookom
MS Access MVP
--

Tom Lake said:
Duane Hookom said:
You can never get a result from comparing Null to any value. Try
Exp1: Nz([FLD1],[FLD2])
Click to expand...

Would

Exp1: IIf(IsNull([FLD1]), [FLD2], [FLD1])

Do it?

Tom Lake
Click to expand...
 
Duane Hookom said:
It should have taken you about 10 seconds to test this (if you had Access
available).
Click to expand...

Yes, I actually knew it would work but I was employing the Socratic method
of asking a question to effect a learning experience rather than elicit information.

Tom Lake
 
I removed the =null from the function and it worked as desired. Thanks for
the suggestions.

Matt

Tom said:
You can never get a result from comparing Null to any value. Try
Exp1: Nz([FLD1],[FLD2])
Click to expand...

Would

Exp1: IIf(IsNull([FLD1]), [FLD2], [FLD1])

Do it?

Tom Lake
Click to expand...
 
I removed the =null from the function and it worked as desired. Thanks for
the suggestions.

Matt

Duane said:
It should have taken you about 10 seconds to test this (if you had Access
available).

Either method should work if FLD1 is actually null and not an empty string.
You can never get a result from comparing Null to any value. Try
Exp1: Nz([FLD1],[FLD2])
Click to expand...
Click to expand...
[quoted text clipped - 6 lines]
Tom Lake
Click to expand...
Click to expand...
 
Back
Top