How to use any part of the field for a partial match in a query by form.

[Fspinelli]

I don't know any other way to explain this but:

I have a query by form (queries my customer table) that by entering a few letters of a company's name in the search field will bring up results in the subform below it. So if I enter "Mel" I get all companies that Beginwith the letters "Mel".

The problem is if the company has two or more names to it, my query only works off of the first three characters. How do I get the search query to look up all companies with any part of the string I enter in the search field? Partial match of the letters I put in the search field.

For instance I want to look up companies that have the word "Globa" or even "glo" in it. Only companies that come up are the companies whose names BEGIN with Glo or Global come up. The company I want, say it's "Mega Global Inc." are not pulled. If I were to even place an "Inc." in the search field all companies with "Inc" anywhere in their name should come up.

I would greatly appreciate any assistance and I thank you!

[nico5038]

You should use the LIKE clause with a "*" before and after the search string.

Nic;o)

[Fspinelli]

Thank you! But where do I put it?

[nico5038]

When you have a query in the WHERE clause, or in a Query By Form just use "*glo*" (without the " ofcourse) in the field.

Nic;o)

[NeoPa]

FSpinelli:
Thank you! But where do I put it?

Essentially, wherever you currently have something that is like :

[ControlName] & '*'

As Nico says, this would need to be changed to :

'*' & [ControlName] & '*'

If it's a '%' instead of a '*', then use that instead of course (ANSI Standards in String Comparisons).