Make Searches Match Any Part of Field

[Ethan]

Hey there,

I have a search form that I enter my search data into, and then a button
that runs the query and returns the results in another form. It works very
well.

What I would like to know, is there a way to make a field non-text specific?

For example, I enter the city name "St. Cloud". This returns results that
are "St. Cloud" exactly, not "Saint Cloud" or "St Cloud". Is there any way I
can make the search match any part of the field, much like the Find Function
(using Ctrl+F)?

For the City field I use the below SQL code:
([City]=Forms!Search.city Or Forms!Search.city IS NULL)

Thanks in advance,

Ethan

 

[John Spencer]

Try something like

([City]LIKE "*" & Forms!Search!city & "*" Or Forms!Search!city IS NULL)

So if you enter CLOUD You will get matches on St. Cloud, Saint Cloud, St
Cloud, StCloud, and Cloud City.



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

 

[Ethan]

Hey,

That worked great! Cloud City, I laughed at that one.

Thanks,

Ethan

Try something like

([City]LIKE "*" & Forms!Search!city & "*" Or Forms!Search!city IS NULL)

So if you enter CLOUD You will get matches on St. Cloud, Saint Cloud, St
Cloud, StCloud, and Cloud City.



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

Hey there,

I have a search form that I enter my search data into, and then a button
that runs the query and returns the results in another form. It works very
well.

What I would like to know, is there a way to make a field non-text specific?

For example, I enter the city name "St. Cloud". This returns results that
are "St. Cloud" exactly, not "Saint Cloud" or "St Cloud". Is there any way I
can make the search match any part of the field, much like the Find Function
(using Ctrl+F)?

For the City field I use the below SQL code:
([City]=Forms!Search.city Or Forms!Search.city IS NULL)

Thanks in advance,

Ethan