iif function syntax problem

  • Thread starter Thread starter angie
  • Start date Start date
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.
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top