iif function syntax problem

[angie]

i have the following expression in the criteria row of my query and it
returns no records. can you help me with the syntax?

IIf([Forms]![Form2-WAREHOUSE]![SPECIAL] Is
Null,[SPECIFIC-TBL.description],[SPECIFIC-TBL.description] Like "*" &
[Forms]![Form2-WAREHOUSE]![SPECIAL] & "*"))

when my form field is null i want the query to return all the records, but
if my form filed contains text i want the query to return all records that
contain my formfield text.

[vanderghast]

At first glance, seems that

IIf

(

Forms![Form2-WAREHOUSE]!SPECIAL Is Null,

True,

[SPECIFIC-TBL].description Like "*" & Forms![Form2-WAREHOUSE]!SPECIAL & "*"

)

Note that you were using [SPECIFIC-TBL.description] where I assume it should
be [SPECIFIC-TBL].[description]: the difference is subtle, but the syntax
TABLENAME dot FIELDNAME should be kept (or [TableName] dot [FieldName], but
not [TableName dot FieldName] )

You also have an extra closing parenthesis.

Vanderghast, Access MVP

"angie" <an...@discussions.microsoft.com> wrote in message
news:DFC862FA-3416-48F9...@microsoft.com...

[angie]

in another query i am using the following expression that works ok:

IIf([Forms]![Form2-WAREHOUSE]![SPECIAL] Is

Null,[SPECIFIC-TBL.description],[Forms]![Form2-WAREHOUSE]![SPECIAL])

i have tried your suggestion but i still get no results.

[KARL DEWEY]

You did not response to the assumption that [SPECIFIC-TBL.description] was in
reality [SPECIFIC-TBL].[description]. What is the actual field and table
name?

Try this --
Like IIf([Forms]![Form2-WAREHOUSE]![SPECIAL] Is Null, "*", "*" &

[Forms]![Form2-WAREHOUSE]![SPECIAL] & "*")

--
Build a little, test a little.

[angie]

this works ok,
thank you!

[Duane Hookom]

I prefer:

Like "*" & [Forms]![Form2-WAREHOUSE]![SPECIAL] & "*"

Or [Forms]![Form2-WAREHOUSE]![SPECIAL] Is Null

--
Duane Hookom
Microsoft Access MVP