Like Statements in Access 2007

[LadyDi]

I am trying to set up a query that will run, based on criteria that can be found in a form. In other words, when I type a customer name in the form and press a button, the query will run and give me information on that customer. I made the customer name field on the form a combo box based on my main table, so that the customer name could not be mis-typed. Then I built the query and used this statement in the criteria section: Like [forms]![Search Form]![Customer Name]. When I select a name on the form and try to run the query, no results are returned. This statement works in a database I set up using Access 2003. I don't know why it won't work with Access 2007. How can I get this query to work in Access 2007? Any assistance you can provide, would be greatly appreciated.

 

[Brianwarnock]

I haven't got 2007 so I cannot comment on that issue but I thought that the syntax would be

Like "*" & [Forms]![FormName]![TextBoxName] & "*"

but if you are using a combo to ensure the name is accurate why are you using Like ?

Brian

 

[LadyDi]

I have tried your suggestions, and am still having problems with my query. I can get that code to work for every field except the customer name. I have no idea why it doesn't like the customer name. I am attaching the database I am working on so that you can see what is happening. The query I am looking at is the one titled "Search". It is linked to the form titled "Search Form". Any assistance you can provide, would be greatly appreciated.

 

[Brianwarnock]

As I said earlier I do not have 2007 so sorry I cannot look at your DB.

Brian

 

[Brianwarnock]

I see that you have sensibly created a new thread so that others realise that this is not being answered. Having read your new post I began to wonder if this is a spaces issue and that you need to consider using Trim, however why things should be different between 2003 and 2007 I leave others to answer.

Best of luck
Brian

 

[vbaInet]

There we go Brian. I've just converted it to 2000 for you in case you still want to get your hands stuck in.

 

[Brianwarnock]

I may take a look tomorrow but Lady Di said it worked in 2003 so maybe I'll learn nothing, but thanks anyway.

Time for the old guy to retire with his book at the moment.

Brian

 

[CraigDolphin]

The combo box on the form is SHOWING the name but STORING the MLA number. So your query criteria is comparing a number to a string.

Either change the combo to store the string, or change the query to use the criteria in the MLA number field.

 

[Brianwarnock]

Can't she just change the bound column to 2 ?

Brian

 

[Simon_MT]

Have a look at this:

Simon

 

[CraigDolphin]

Can't she just change the bound column to 2 ?

Brian

Would that not qualify as changing the combo to store the string?

 

[Brianwarnock]

Would that not qualify as changing the combo to store the string?

She is storing both the MLA and the name but binding on the MLA.

Brian

 

[Simon_MT]

The Search Form as it was in the version does not need to bound at all. The Search From is simply that and can store unbound textboxes or combiboxes.

To Search You need to combine MLA/SLS/SPSA. I take out the Criteria from the Query and create a criterai in a Function. Have a look at Post #10.

Simon

 

[LadyDi]

Changing the bound column to 2 did the trick. Thank you all for your assistance with this. I really appreciate it.

 

[HiTechCoach]

Changing the bound column to 2 did the trick. Thank you all for your assistance with this. I really appreciate it.

If it were me I would NOT just change the bound column. I would fix the query so that it use the primary key field like the combo box.

I believe using the primary key to be a "best practice" method.

See: Your other post on subject

 

[Brianwarnock]

Glad you found this Coach, you are correct of course and thinking about it Craig suggested the same thing but not so clearly. Guess I've been too long retired and aren't thinging things through, perhaps time to stick to the non access issues before I lead people into bad habits.

Brian