Attempting to find records on partial match 1

[Kepton]

In the database I am working on, each record is given a location. The problem is the locations are fairly detailed, making exact matches long and returning only one or two results.

Is there any way to search the location field for certain keywords?

Example:

Item 1 location = "Room1 first shelf"
Item 2 location = "Room1 second shelf"
Item 3 location = "Room2 first shelf"

I would like to search based on "Room1", returning only the first 2 items.

Thanks

 

[traingamer]

try:

select ... 
where [location] LIKE '*Room1*'...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Kepton]

Thanks for the quick reply, but no luck.

Some additional information:

the location being looked for is pulled from a form, so currently I have something like this

SELECT...
WHERE [table].[location] LIKE [forms].[myform].[locationSearch]

I've tried a few variations as well, such as

SELECT...
WHERE [table].[location] LIKE '*' & [forms].[myform].[locationSearch] & '*'

Still doesn't work, any ideas?

 

[SecondToNone]

Try this:

SELECT...
WHERE

.[location] LIKE '*" & [forms].[myform].[locationSearch] & "*'

-SecondToNone

 

[Kepton]

Still doesn't work. Is there any info I've missed that could help?

 

[traingamer]

How about

WHERE [table].[location] LIKE '*" & [forms].[myform].[locationSearch] & "*'[COLOR=red]"[/color]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SecondToNone]

Can you write out your query to see what actual query is being passed to the database...

-SecondToNone

 

[Kepton]

traingamer: trying that gave an error.

SELECT [idNumber]
FROM items
WHERE items.[location] Like '*" &  [Forms].[products].[itemSearch] & "*';

ItemSearch is a combobox which draws values from a seperate table. This table has all the areas items are kept.

 

[SecondToNone]

Try this and let us know if it works..

SELECT [idNumber]
FROM items
WHERE items.[location] Like '*someItem*';

i mean just hardcode..then we can see where the problem lies...

Also try using % instead of *...not sure though...

-SecondToNone

 

[Kepton]

SELECT [idNumber]
FROM items
WHERE items.[location] Like '*areaName*';

where "areaName = area with items in it"

brings up all items that have that area anywhere in their location field.

 

[SecondToNone]

Did you try the % instead of *

-SecondToNone

 

[traingamer]

Have you tried

[COLOR=red]"[/color]SELECT [idNumber]
FROM items
WHERE items.[location] Like '*" &  [Forms].[products].[itemSearch] & "*'[COLOR=red]"[/color];

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Kepton]

Adding "'s to the statement threw an error.

Using %'s didn't throw an error, but returned no results.

 

[PHV]

And this ?
SELECT [idNumber]
FROM items
WHERE items.[location] Like '*' & Trim([Forms]![products]![itemSearch]) & '*';

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

 

[Kepton]

Thanks PHV!

That mostly did it, there are a few blanks still but I should be able to figure them out.

I am assuming Trim works like the trim in VB, as in removes whitespace before and after the string. Does access add whitespace somehow? After double checking I didn't see any extra leading or trailing in the input.

 

[traingamer]

Duh, didn't see the forest 'cause of all those darn trees.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill