IIF statement problem

  • Thread starter Thread starter Harris
  • Start date Start date
H

Harris

Can anyone help?!

I am having difficulty with a wildcard query.

IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[Forms]![WorstCritAcrossHosp]![SelectServiceElement],
Like "*")


The IIF statement checks to see if a certain option box on a form is
selected (1), and if so, uses the value in a drop down box called
SelectServiceElement. This drop down box value can be from 1 to 50. If
the option box is not selected, all the values from 1-50 should come
up in the query. I have tried like '', like *, >1, but nothing seems
to work. If I replace Like "*" with say, 7, then the query functions
correctly. Anyone know what the problem is?

Thanks
Harris

IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[Forms]![WorstCritAcrossHosp]![SelectServiceElement],
Like "*")
 
It sounds like you're putting this expression on a Criteria line, it won't
work. The SQL probably looks like:

WHERE MyField =
IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[Forms]![WorstCritAcros
sHosp]![SelectServiceElement], Like "*")

So, when ServicesReport is not 1, you end up with

WHERE MyField = (Like "*")

Remove the criteria from under your field. In a blank Field box, enter
something like:

IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[MyField] =
[Forms]![WorstCritAcrossHosp]![SelectServiceElement], [MyField] Like "*")

Uncheck the Show box and place TRUE on the Criteria line.

([MyField] is the name of the field you're trying to test.)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi John,

You are absolutely brilliant! Thanks very much, worked without any further
fiddling.

Much appreciated for saving the few hairs left on my head.

Regards,
Harris

It sounds like you're putting this expression on a Criteria line, it
won't
work. The SQL probably looks like:

WHERE MyField =
IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[Forms]![WorstCritAcros
sHosp]![SelectServiceElement], Like "*")

So, when ServicesReport is not 1, you end up with

WHERE MyField = (Like "*")

Remove the criteria from under your field. In a blank Field box, enter
something like:

IIf([Forms]![WorstCritAcrossHosp]![ServicesReport]=1,[MyField] =
[Forms]![WorstCritAcrossHosp]![SelectServiceElement], [MyField] Like "*")

Uncheck the Show box and place TRUE on the Criteria line.

([MyField] is the name of the field you're trying to test.)
Click to expand...
 
Back
Top