Like Parameter acts different - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Like Parameter acts different

Gregg Moore

5 years ago

I have a database that has a search form with several combo boxes to filter a subform. It has been working great however when I split the database and put the backend on SharePoint I noticed that the filters weren't working as they normally do. For example trying to filter an ID field with the 'LIKE "*"&[CustomerID]&"*"' When I tell it to filter a customer with ID # 5 it will also display customers with any ID that contains the number 5 in it.(58 etc) Any suggestions to remedy this?

	Adam Schwanz @Reply 5 years ago
I'm confused, that is how like works normally. You're saying 5 when you type that in, which says I want anything (or nothing) in front of the 5, and anything (or nothing) after. It should return 5, 15, 52, 59, 13234043920543211 etc.. If you want it to ONLY return 5 you should just say CustomerID=5 not LIKE with wildcards.

	Gregg Moore OP @Reply 5 years ago
Sorry for the confusion. I have a combo box in the past which passes information to a parameter query that if nothing were in the combo box it showed all records but if I used the combo box to look up a customer name from another table it would populate only records that matched that customer now it will not filter to just that customer. it contains records of other customers as well.

	Gregg Moore OP @Reply 5 years ago
Because it is a multi field search form I need to be able to leave that field blank when trying to filter from other combo box fields and not affect the filter.

Adam Schwanz @Reply

5 years ago

Are you using the multi-field search from the search seminar?

You could change it from customerID to the actual customer name (Bob's Shop instead of 5 for example) by bringing in the table with the real name into the query, or making a DLOOKUP expression and basing that search off that.

If you're using the multi-field search from the seminar, just change that to CustomerID= instead of the LIKE, If the combo box is empty it will not apply that to the end SQL statement.

	Richard Rost @Reply 5 years ago
SharePoint searches are weird. You can only use the * for prefix matching. So you can search for Acc* which will turn up results like Access, Accelerate, and so on. But you can't do acc. It's stupid, and one of the reasons I don't like SharePoint.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View