iif function syntax problem

A

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

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
 
A

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.


vanderghast said:
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 said:
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.
Click to expand...
Click to expand...
 
K

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 said:
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.


vanderghast said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
A

angie

this works ok,
thank you!

KARL DEWEY said:
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 said:
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.


vanderghast said:
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

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I prefer:
Like "*" & [Forms]![Form2-WAREHOUSE]![SPECIAL] & "*"
Or [Forms]![Form2-WAREHOUSE]![SPECIAL] Is Null


--
Duane Hookom
Microsoft Access MVP


KARL DEWEY said:
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 said:
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.


vanderghast said:
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

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date comparison in IIF statement in a query 1
IIF statement in criteria not working 1
If statement that returns all entries or one 2
Criteria to return all records if selection from form is null 3
Using IIF statement in Criteria 3
IIF with option button 2
iif in the criteria 3
Iif statement and secondary evaluation criteria 1

Top