Parameter Query Help Needed

[Lynnemcc87]

I am currently creating a database at my work which holds details of over 200 companies. I was wondering if there is any possible way to create a parameter query that returns ALL or PART of the company name that is being searched for.

for example.

The parameter query at the minute prompts the user to 'Enter Company Name'.....if I were to type in 'Royal Bank of Scotland' it will return the records for this company, which is great! However, if I type in 'Royal Bank' then it does not return any search results



We also have alot of company departments on our database as they all have different contacts. For example, we have records such as: Falkirk Council Home Enery Advice Team, Falkirk Council Housing, Falkirk Council Healthier Lives......so if i wanted to pull up ALL of the records that have Falkirk Council as part of their company name, then it doesnt work and the search comes up blank becaue there are no companies that are only called Falkirk Council.

If anyone is able to help me on this then that would be great and very much appreciated!

[rivereridanus]

Absolutely. You can just use a wildcard such as *. If you are writing your own SQL string for that search, then you can just do something like "SELECT * FROM [datasource] WHERE [name] LIKE *" + [whatever is in your textbox] + "*;"

The * on both sides will allow you to search any part of that field.

[Lynnemcc87]

Thanks so much for getting back to me on this and letting me know that this can be done!

However, I'm slightly confused as to where I write the string that you suggested above. Do i write this in the criteria box in the query design under the field 'Company'? I'm a good user of Access but unfortunately I am not at an advanced level Also, which parts of the string do i amend by writing in my own criteria? the database is quite simple, the table is called Contacts and the field that I want to be able to search in is called Company.

[ItsMe]

...The parameter query at the minute prompts the user...

As mentioned in post #2, employ the LIKE operator and the Wildcards.

Since your query object is requesting the user input, open your query and switch to SQL view. Locate the statement for the parameter. Change the = sign to LIKE and add the wildcards as described in post #2.

[Lynnemcc87]

Thanks, thats helps, I switched it to the SQL view and changed the = to LIKE.....im still not getting anywhere with it though. Im getting confused with the order in which I should write it all and where I should enter my own text. It keeps coming up with an error message basically saying that what I'm writing is incorrect

[ItsMe]

Go ahead and post the SQL that was previously working here.

[Lynnemcc87]

Sorry, was on my lunch break.

WHERE (((Contacts.Company)=[Enter company name]));

That is the SQL that was working when you eneter the full name of the company.

Thanks!

[ItsMe]

I forgot that the input box does not have quotes in the SQL statement so I added them here. Basically, have to concatenate the wildcards to the input box.

WHERE (((Contacts.Company)like"*" & [Enter company name] & "*"));

[Lynnemcc87]

I forgot that the input box does not have quotes in the SQL statement so I added them here. Basically, have to concatenate the wildcards to the input box.

WHERE (((Contacts.Company)like"*" & [Enter company name] & "*"));

Thanks very much! The parameter query now works! This will make things so much easier for my team when using and searching through the database!

Again, Thanks so much for your help, it is very much appreciated

[ItsMe]

Not a problem and welcome to the forum.