Using "Like" and Referencing a Form

[geraldcor]

Hello all,
Is there a way to use the "like" statement in a WHERE clause but the like you are referencing is a field on a form? Example: Under the criteria of a query: Like "Forms!frmEmailListByCompany!CompanyCombo*". The bosses want to be able to choose a company from a list and print all emails associated with that company. The problem is some company are listed as like Company International, Company textiles, Company blah, etc. So they want to be able to select company and have all associated companie's contact's emails print. Is there another way besides the like statement? It doesn't like it when I reference a field on a form with like (yes is it open). I am lost. Thank you for any help.
Greg

 

[Pat Hartman]

You don't have the syntax right:

Where SomeField Like Forms!frmEmailListByCompany!CompanyCombo & "*";

This may not work for you though because it requires that the name field start with "something". If you want to look for "something" where ever it occurs in the field, try:

Where SomeField Like "*" & Forms!frmEmailListByCompany!CompanyCombo & "*";

 

[geraldcor]

That didn't quite work. I at least don't ge blank queries. It only shows the one compnay from the combo list. Shouldn't that name in the combo be enough for it to recognize others with that same name. The company is Pacific resourcing, shouldn't it be able to recognize Pacific Accounts Recievable and so on? Perhaps I need to try something else to display this info. Thanks for you speedy reply. Any more help on this?
Greg

 

[Ukraine82]

Try something like this

Like "*" & [forms]![frmEmailListByCompany]![CompanyCombo] & "*"

Instead of typing the company word you can type in international or textiles.

hth,
Michael

 

[geraldcor]

So here is an update of what I have done. I got it to work once. I did some futzing and now it doesn't work. I think it has something to do with the way I am setting up my combo box on the form the qry is referencing but I can't figure out what made it work that one time. From what I remember, I set the combo box equal to some field that I have in another query that the form is based on. I typed in a shortened company name and it seemed to add it to the combo list and lo and behold it worked. But I was worried I was adding stuff to a field that I didn't want stuff added to so I changed it and now nothing. How could the combo be influencing the like statement like that? I am reaching burnout.
Thanks for the replies.
Greg

 

[Pat Hartman]

When using a fuzzy search, you shouldn't be using a combo. The combo returns the entire name so searching for -
Like Pacific resourcing* will NOT return Pacific Accounts Recievable. You would have to enter:
Like Pacific* to retrieve both companies.

 

[APKnapp]

Try something like this

Like "*" & [forms]![frmEmailListByCompany]![CompanyCombo] & "*"

Instead of typing the company word you can type in international or textiles.

hth,
Michael

Any idea why I'm getting a bunch of 'enter parameter value' pop-ups when I use fuzzy searches like this in my query?

 

[Muzi&Con]

Thank you all very much.
Pat Hartman's answer really helps me.

 

[Pat Hartman]

You're welcome