Wild card Query

[SA812]

Hello my Tek Tip Masters!

I've built a select query with a wild card search, dug up from this forum using the search feature.

My wild card search is on [Customer Name] which is linked to a Access query link to a SQL table(s).

I've tried the following:
like "*" & [enter customer name]
works fine but only searches front part of name

I've tried this:
like "*" & [enter customer name] & "*"
Let me search the front and the rear part of customer name

My problem is i have a customer name called "FEI Denver"
if i search for "denver" i get the correct answer. If i search for "FEI" i get the correct answer. But when i search for "FEI Denver" i get nothing.

I've also tried the following:
like "*" & [enter customer name]
"or" like "*" & [enter customer name] & "*"
"or" like [enter customer name] & "*"

They work with different parts of the name but if someone types the whole name ei; "FEI Denver" it returns no result.

Thanks in advance for your help and patience
SA

 

[dhookom]

If the record is not returned then I expect you have a misconception about either the stored value in the table or the parameter entered in the query. Perhaps there are two spaces entered into the table. "FEI Denver"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Good catch Duane

What happens if you enter

FEI*Denver

?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[SA812]

FEI*Denver Returns everything. Im guessing that proves there is more than one space between the word correct?
There is no way to tell the query to ignore the spaces?

 

[Golom]

It proves that there's something between them. It may be multiple spaces or some non-displayable character. I guess you could do something like

LIKE "*" & Replace([enter customer name]," ","*") & "*"

That still won't pick up non-display characters.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]