Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Resolved Question

Problem with Access inquiries using "Like"

Jan 26, 2015 6:12AM PST

I've been having occasional problems with queries that use the "Like" operator in Access 2010. For example, when I use the query builder and type: Like"*" 7 (Enter Description) as my criteria, everything works just fine for a while. However, once I've been using the database for several days, it makes a sudden change. When I run the query, I'll get not one dialogue box that asks me to enter my description, but two. If I clear the paraeters of the query, save it, and then reenter those same parameters, the problem stops, for a short time, but over time, it happens again and again. I need the "*" operator before and after the parameter as those using the data base usually don't know that they can include it. Is there a fix for this problem? A patch that can be downloaded? Or a better way of writing the query? I've tried Like (Enter Description) & "*" but had the same problem. Thank you, in advance for your help and asssistance.

Discussion is locked

Garry Kessel has chosen the best answer to their question. View answer

Best Answer

- Collapse -
Re: Like
Jan 26, 2015 6:22AM PST

Both
Like"*" & (Enter Description)
and
Like (Enter Description) & "*"
seem like invalid invalid string concatenations, because (Enter description) isn't a string.

Moreover it's quite unclear what you want to do. Parameters are treated as a field, so they in the query they become part of the field list in the select, the like becomes part of the where clause.


Kees

- Collapse -
Re: Like
Feb 21, 2015 4:07AM PST

I am posting on behalf of a friend who uses a computer in his work but does not have internet service. He made an error when he sent me the original inquiry; here is come updated and corrected information. i don't understand this so I am typing his question verbatim-

Thanks for taking the time to respond, and I apologize for the msiprint. It should have been a bracket [] not a parenthesis (). When properly done, the criteria section in the query builder says Like [enter description] & "*". I've also used Like "*" & [enter description] & "*" but each time, I have the same problem. At first, everything works just fine. When the user goes to use that query or form using that query they get a dialogue box asking them to enter a desciption with the query automatically entering the asterisk "*"operator, but over time instead of being asked only once, they're asked twice back to back. If I open the query bulder, clear and then save the criteria field and then reenter my Like [enter description] & "*" into the criteria field, everything will once again work fine for a while.

- Collapse -
That free version is adware
Feb 21, 2015 4:27AM PST

The freeware is ad-supported; you need to sign up every couple of weeks for a new sponsor to keep using it.

- Collapse -
Strange indeed.
Feb 22, 2015 5:09AM PST

It looks valid and I see no reason why it would degrade over time. However, parameter queries are not standard SQL, and it's possible it's a bug in MS Access.

Things to try:
1. See if MS happened to fix in MS Access 2013 (unlikely, but possible)
2, Contact Microsoft and report it as a bug.
3. As a work-around, delete and rebuild the query via an Autostart macro. Then (if "over time" means "in a few days") you'll probably never notice it.
4. Another work-around (if not a solution): let the user type the search field in the form, as is common for search fields. Then in an on-change event fill a hidden field with "*" & field & "*" (maybe trim(field) is even better). In the query, use like [forms]!{thisform]![thishiddenfield]. Might very well work, and moreover, I find it more user friendly option than such a popup for a parameter query.

Kees

- Collapse -
Re; Strange Indeed
Mar 21, 2015 6:30AM PDT

Thanks for taking time to help. I tried your suggestion (automacro) whcih seems to be working so far. I suspect thatt he problem is, as you suggested, a ug in he software. I believe this is becuase this isn't the only time something works properly, even perfectly for a while only to later begin acting screwy. It's a bit difficult to explain but its either a bugh in Access or some sort fo corruption that occurs over time either within teh installation fo Access or the data base itself which isn't being fixedd by teh Compact abnd Repair" utility. Either way, i appreciate you takingt time out to answer my question.