Help with Search using LIKE 1

[bcw8]

Hi,

I was wondering if anyone would be able to help me
out with a problem. I have a table named Schedule in
a MS Acess DB. I am trying to find all games that
contain the word "Miami". I will be passing this
parameter through an access form. Below is the
current sql I am using to find the results below. I
am able to find the results below, but I am not able
to find "Miami at ......" Can anyone please give me
some advice. Thanks.


Denver at Miami
Carolina at Miami
Kansas City at Miami
Atlanta at Miami
New England at Miami
Buffalo at Miami
N.Y. Jets at Miami
Tennessee at Miami


SELECT *
FROM Schedule
WHERE (((Schedule.Game) Like "*" & Forms![Find

Game]!StartCombo))
ORDER BY Schedule.Week;

 

[DotNetGnat]

should be something like this...

SELECT * FROM mytable WHERE Game LIKE '*Miami*'

so adapt your code to that...

-DNG

 

[PHV]

WHERE Schedule.Game Like '*' & Forms![Find Game]!StartCombo & '*'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bcw8]

PHV,

I really appreciate your posting and you sharing your advice/knowledge with me. You were a great help. Thank you for helping me solve this problem.

Bcw8

 

[DotNetGnat]

I suggested the same thing Bcw8

-DNG

 

[bcw8]

DNG,

I didn't want to hard code the search criteria. I wanted to pass different parameters into the form. For example Arizona, Dallas, Washington, etc.. different cities. that would give me:

Arizona at Seattle
St. Louis at Arizona
Philadelphia at Dallas
Washington at Miami

Thanks for your post though.

-Bcw8

 

[DotNetGnat]

i was just showing the sample format of your sql should look like...and single quotes was all that needed for your query to work fine which i showed in the sample query...

anyways...glad to be of help

-DNG

 

[ijitnoin]

Select CASE WHEN A < B THE 'C' ELSE 'D' END AS [Y]

 

[lespaul]

just to clarify why it wasn't working and what the difference is:

your original query:

WHERE (((Schedule.Game) Like "*" & Forms![Find Game]!StartCombo))

only has the * at the beginning of the string. That means it is only going to find records where the END of the field contains your string.

If you had put it like this with the * at the END of the string:

WHERE (((Schedule.Game) Like Forms![Find Game]!StartCombo & "*"))

you would have found all matches where the BEGINNING of the field contains your string.

By enclosing the criteria in *,

WHERE (((Schedule.Game) Like "*" & Forms![Find Game]!StartCombo & "*"))

you find all records where the field contains your string in any place.

Hope that helps to understand why one worked and the other didn't.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design