Help please on the IIF command in a query criteria

  • Thread starter Thread starter KZ
  • Start date Start date
K

KZ

I am trying to us the IIF command in my criteria to allow sorting.
This is a simplified version of the statment that still fails.

IIf(1=1,([Table1].[data]) Like "*","a")

1=1 is just to force TRUE for testing.
"a" is also just junk in the real thing it is tied to a form data.
The thought was that I should see all values in Table1 feild data just like
if you put

([Table1].[data]) Like "*"

into the Criteria box directly.

Thanks alot to anyone that can help.
 
If you look at the SQL that's being generated behind the scenes (use the
View menu while you've got the query open in Design mode), you'll see why
that doesn't work. What you're actually getting when you put that expression
as a criteria is something along the lines of

WHERE Field = IIf(1=1,([Table1].[data]) Like "*","a")

which, of course, is invalid.

Try putting

Like IIf(1=1, "*", "a")

as the criteria.
 
You need to try "&" to concantenate the * with variable text. For example, in
your criteria: like [field]&"*" or Like "*" & [field] & "*" to search for
that field anywhere in the data.
 
Thanks for the help.

Like IIf(1=1, "*", "a")

Is the only combination that seems to work.



Margaret said:
You need to try "&" to concantenate the * with variable text. For example,
in
your criteria: like [field]&"*" or Like "*" & [field] & "*" to search for
that field anywhere in the data.

KZ said:
I am trying to us the IIF command in my criteria to allow sorting.
This is a simplified version of the statment that still fails.

IIf(1=1,([Table1].[data]) Like "*","a")

1=1 is just to force TRUE for testing.
"a" is also just junk in the real thing it is tied to a form data.
The thought was that I should see all values in Table1 feild data just
like
if you put

([Table1].[data]) Like "*"

into the Criteria box directly.

Thanks alot to anyone that can help.
Click to expand...
Click to expand...
 
Back
Top