Like inside Iif as Query Criteria

[Patrick A]

All -

I have a query in which I want to look at a form, and match against 1
value if a specific value has been selected from a list box, or another
value if it has not. I'll have similar criteria in several columns,
checking to see if a specific column should be searched.

In english, my logic goes like this:

If(ListBoxSelection="Name", then Use ListBoxSelection As Criteria, else
Use Other Criteria)

I have this working for exact text matches:
IIf([Forms]![Main]![llb_SearchMe]="Name",([Forms]![main]![tb_Search]),([Forms]![main]![lb_Data_Model]))

But of course, I want to do the search for "near matches," using "Like"
to do the job. Elsewhere, I have no problem using Like, which I have
constructed so:

Like "*" & ([Forms]![main]![tb_Search]) & "*"

But no matter how I add "Like" to my Iif statement, I get no results.
I get no error, but I get no results.

I've tried...and tried...and tried...and tried...

IIf([Forms]![Main]![llb_SearchMe]="Name",(Like "*" &
([Forms]![main]![tb_Search]) & "*"),([Forms]![main]![lb_Data_Model]))

IIf([Forms]![Main]![llb_SearchMe]="Name",(Like "*" &
[Forms]![main]![tb_Search] & "*"),([Forms]![main]![lb_Data_Model]))

IIf([Forms]![Main]![llb_SearchMe]="Name",Like "*" &
([Forms]![main]![tb_Search]) & "*",([Forms]![main]![lb_Data_Model]))

IIf([Forms]![Main]![llb_SearchMe]="Name",Like "*" &
[Forms]![main]![tb_Search] & "*",([Forms]![main]![lb_Data_Model]))

IIf([Forms]![Main]![llb_SearchMe]="Name",Like "*" &
"[Forms]![main]![tb_Search]" & "*",([Forms]![main]![lb_Data_Model]))

Any Ideas?

Patrick

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Where are you putting this IIf() function, in the WHERE clause of the
query? If so you need to format it like this:

WHERE IIf([Forms]![Main]![llb_SearchMe] = "Name",
column_name Like "*" & [Forms]![main]![tb_Search] & "*",
column_name = [Forms]![main]![lb_Data_Model])

Substitute your column name for "column_name."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaBYYechKqOuFEgEQJl3gCdEG6YAXukYZWtlUdoSU5So6 f7vy0AoMWD
0RikTd0OSusjiYm6+kw0z5Yq
=KRXE
-----END PGP SIGNATURE-----

Patrick A wrote:
< SNIP >

I have this working for exact text matches:
IIf([Forms]![Main]![llb_SearchMe]="Name",([Forms]![main]![tb_Search]),([Forms]![main]![lb_Data_Model]))

But of course, I want to do the search for "near matches," using "Like"
to do the job. Elsewhere, I have no problem using Like, which I have
constructed so:

Like "*" & ([Forms]![main]![tb_Search]) & "*"
< SNIP >
IIf([Forms]![Main]![llb_SearchMe]="Name",(Like "*" &
([Forms]![main]![tb_Search]) & "*"),([Forms]![main]![lb_Data_Model]))

< SNIP >

[Patrick A]

I'm trying to type it in the Criteria Row of the Design View of the
Query.

I'm typing in:

IIf([Forms]![Main]![llb_SearchMe]="Name",Like "*" &
[Forms]![main]![tb_Search] & "*",[Forms]![main]![lb_Data_Model])

The resulting SQL looks like this:

WHERE
(((METADATA_REPORT_DATA_FIELD.COLUMN_NAME)=IIf([Forms]![Main]![llb_SearchMe]="Name",(METADATA_REPORT_DATA_FIELD.COLUMN_NAME )
Like "*" & [Forms]![main]![tb_Search] &
"*",[Forms]![main]![lb_Data_Model])));

But I get no results.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You didn't format the WHERE clause as I suggested. Here it is w/ your
column name (do this in the SQL View - it's easier than the QBE design
grid):

WHERE
IIf([Forms]![Main]![llb_SearchMe]="Name",
METADATA_REPORT_DATA_FIELD.COLUMN_NAME
Like "*" & [Forms]![main]![tb_Search] & "*",
METADATA_REPORT_DATA_FIELD.COLUMN_NAME=[Forms]![main]![lb_Data_Model])

Is there really a column named "COLUMN_NAME" in your table
"METADATA_REPORT_DATA_FIELD"? Not a very descriptive name. I could say
the same about your table name. It appears as if you've got some major
craziness going on there. :-O
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaVHYechKqOuFEgEQLfEwCfeu6rUPdWDRO2UDZVX4DyC2 +6SyUAn0Zq
tH5d1w2GebNAFZDy7yZDrKBN
=D1IE
-----END PGP SIGNATURE-----

Patrick A wrote:

I'm trying to type it in the Criteria Row of the Design View of the
Query.

I'm typing in:

IIf([Forms]![Main]![llb_SearchMe]="Name",Like "*" &
[Forms]![main]![tb_Search] & "*",[Forms]![main]![lb_Data_Model])

The resulting SQL looks like this:

WHERE
(((METADATA_REPORT_DATA_FIELD.COLUMN_NAME)=IIf([Forms]![Main]![llb_SearchMe]="Name",(METADATA_REPORT_DATA_FIELD.COLUMN_NAME )
Like "*" & [Forms]![main]![tb_Search] &
"*",[Forms]![main]![lb_Data_Model])));

But I get no results.