469,306 Members | 1,556 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

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

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!
Oct 14 '10 #1
4 14729
3,080 Expert 2GB
You should use the LIKE clause with a "*" before and after the search string.

Oct 14 '10 #2
Thank you! But where do I put it?
Oct 14 '10 #3
3,080 Expert 2GB
When you have a query in the WHERE clause, or in a Query By Form just use "*glo*" (without the " ofcourse) in the field.

Oct 14 '10 #4
32,173 Expert Mod 16PB
Thank you! But where do I put it?
Essentially, wherever you currently have something that is like :
Expand|Select|Wrap|Line Numbers
  1. [ControlName] & '*'
As Nico says, this would need to be changed to :
Expand|Select|Wrap|Line Numbers
  1. '*' & [ControlName] & '*'
If it's a '%' instead of a '*', then use that instead of course (ANSI Standards in String Comparisons).
Oct 14 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Mike Cocker | last post: by
2 posts views Thread by Christian Staffe | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.