Wildcards in IIF statements

E

elmo2

In Access 2003, I would like to include wildcards (eg "*") as part of some
iif functions (eg in queries) so that if (say) the option group number = 1
then all records will be shown, whereas if option group number = 2 only
records with a specific entry (in the field in question) will be shown.

When I try and do this I get a message "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. (Error 3071)"

What is the correct syntax to use / how else to solve?

Current expression reads:
=IIf([Forms]![F Current Proposals and
Inquiries]![OfficeSpecificSelector]=1,"*","Melbourne")
 
A

Andi Mayer

In Access 2003, I would like to include wildcards (eg "*") as part of some
iif functions (eg in queries) so that if (say) the option group number = 1
then all records will be shown, whereas if option group number = 2 only
records with a specific entry (in the field in question) will be shown.

When I try and do this I get a message "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. (Error 3071)"

What is the correct syntax to use / how else to solve?

Current expression reads:
=IIf([Forms]![F Current Proposals and
Inquiries]![OfficeSpecificSelector]=1,"*","Melbourne")
Click to expand...

do you use it in a Where clause?

then you change the whole clause:
if group number =1 WHERE True
if group number =2 WHERE FieldName="Melbourne"
 
F

fredg

In Access 2003, I would like to include wildcards (eg "*") as part of some
iif functions (eg in queries) so that if (say) the option group number = 1
then all records will be shown, whereas if option group number = 2 only
records with a specific entry (in the field in question) will be shown.

When I try and do this I get a message "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. (Error 3071)"

What is the correct syntax to use / how else to solve?

Current expression reads:
=IIf([Forms]![F Current Proposals and
Inquiries]![OfficeSpecificSelector]=1,"*","Melbourne")
Click to expand...

Where [YourTable].[FieldName] Like IIf([Forms]![F Current Proposals
and Inquiries]![OfficeSpecificSelector]=1,"*","Melbourne")
 
J

John Vinson

In Access 2003, I would like to include wildcards (eg "*") as part of some
iif functions (eg in queries) so that if (say) the option group number = 1
then all records will be shown, whereas if option group number = 2 only
records with a specific entry (in the field in question) will be shown.

When I try and do this I get a message "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. (Error 3071)"

What is the correct syntax to use / how else to solve?

Current expression reads:
=IIf([Forms]![F Current Proposals and
Inquiries]![OfficeSpecificSelector]=1,"*","Melbourne")
Click to expand...

An alternative is to use

WHERE (<fieldname> = "Melbourne" AND
[Forms]![F Current Proposals and Inquiries]![OfficeSpecificSelector]
<> 1)
OR
([Forms]![F Current Proposals and Inquiries]![OfficeSpecificSelector]
= 1)

John W. Vinson[MVP]
 
Top