iif Function in Query

[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

 

[Duane Hookom]

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

 

[Tom Lake]

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

Would

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

Do it?

Tom Lake

 

[Duane Hookom]

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
--

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

Would

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

Do it?

Tom Lake

 

[Tom Lake]

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

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

 

[mattc66 via AccessMonster.com]

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

Matt

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

Would

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

Do it?

Tom Lake

 

[mattc66 via AccessMonster.com]

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

Matt

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])

[quoted text clipped - 6 lines]

Tom Lake

 

[Duane Hookom]

I hadn't noticed that you weren't the original poster. My bad.