Like / IIF issues (as query criteria)

[sha123]

I have been working on this for hours and still cant figure it out....

I have a combo box that selects a description call "land"

I want my query to be able filter the results based on the selection of the
Combo box. The problem I have here is that when I select land, I want it to
be able to produce the records that have "land (large)" and "land (small)"
and "land (medium)"

I have tried all combinations of the IIf and Like statement, but none of them
work.....

here are the ones I have tried

Like IIf([Forms]![Front Page]![SiteTypeCBO]="land","*",[Forms]![Front Page]!
[SiteTypeCBO]

Like IIf([Forms]![Front Page]![SiteTypeCBO]="land","land*",[Forms]![Front
Page]![SiteTypeCBO]

IIf([Forms]![Front Page]![SiteTypeCBO]="Land", Like "land*",[Forms]![Front
Page]![SiteTypeCBO]

plus mane more of this variation....any help is appreciated

Also if there is an easier way to do it in VBA id love to know

Thanks!

 

[Dale_Fye via AccessMonster.com]

How about:

WHERE [FieldName] Like "*" & [Forms]![Front Page]![SiteTypeCBO] & "*"

This would filter those records where the term "land" (or whatever is
displayed in your combo box) is found anywhere within the field.

When I use combo boxes like this, I generally include a option for "All", in
which case you would want the WHERE clause to look something like:

WHERE [Forms]![Front Page]![SiteTypeCBO] = "All"
OR [FieldName] Like "*" & [Forms]![Front Page]![SiteTypeCBO] & "*"

HTH
Dale

I have been working on this for hours and still cant figure it out....

I have a combo box that selects a description call "land"

I want my query to be able filter the results based on the selection of the
Combo box. The problem I have here is that when I select land, I want it to
be able to produce the records that have "land (large)" and "land (small)"
and "land (medium)"

I have tried all combinations of the IIf and Like statement, but none of them
work.....

here are the ones I have tried

Like IIf([Forms]![Front Page]![SiteTypeCBO]="land","*",[Forms]![Front Page]!
[SiteTypeCBO]

Like IIf([Forms]![Front Page]![SiteTypeCBO]="land","land*",[Forms]![Front
Page]![SiteTypeCBO]

IIf([Forms]![Front Page]![SiteTypeCBO]="Land", Like "land*",[Forms]![Front
Page]![SiteTypeCBO]

plus mane more of this variation....any help is appreciated

Also if there is an easier way to do it in VBA id love to know

Thanks!

 

[John Spencer]

If you want an exact match unless the combobox has LAND as the selection, then
use the following

Like IIf([Forms]![Front Page]![SiteTypeCBO]="land","*Land*",[Forms]![Front
Page]![SiteTypeCBO])

Of course another option would be to set the combobox up with TWO columns and
hide the bound column. So column 1 would have what you want the user to see
and column two (the bound column) would hold what you want to search for. Your
combobox would have two columns something like the following

Land : *LAND*
Water : Water
Air : Air
Fire : Fire
Earth : Dirt (ok so I'm kidding)
Swamp : Ground Water

Then the criteria would
Like [Forms]![Front Page]![SiteTypeCBO]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County