Query Search Field Question - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query Search Field Question

Lee Shastid

4 months ago

I have a search field on my main menu to search for items in my home inventory. This is my sea5rch criteria in my query. Like "*" & [forms]![mainmenuf]![searchtextq] & "*". It seems to find anything that i find that it has some kind of match for. But if I put a letter like Z for instance which is not in any product description it returns all of my products. Can someone tell me what I am not doing correctly. I thought it was working fine until i accidently hit the z and enter button and got all the returns.

	Richard Rost @Reply 4 months ago
That's weird. It shouldn't do that. If you have `LIKE "Z"` And there's no Z anywhere in your data, you should get no records. How about some screen shots? Did you try it with just a regular query WITHOUT the form?

	Lee Shastid OP @Reply 4 months ago

	Lee Shastid OP @Reply 4 months ago

	Lee Shastid OP @Reply 4 months ago

	Kevin Robertson @Reply 4 months ago
Do any of the other fields have criteria that may be finding a 'z'? What happens if you remove the criteria from other fields?

	Lee Shastid OP @Reply 4 months ago
Kevin I have removed the criteria from all fields except the description field. It still does it like that. There are only 2 products in the table period. Here are screenshots of the forms for those products.

	Lee Shastid OP @Reply 4 months ago

	Lee Shastid OP @Reply 4 months ago

	Lee Shastid OP @Reply 4 months ago
I have also ran the compact and repair a couple of times.

	Donald Blackwell @Reply 4 months ago
Off the wall thought, does your query and your text box have the same name? You showed a query with "SearchTextQ" in the titlebar and your search string is calling a field named "searchtextq". Just wondering if access is getting confused by a query with criteria calling that same query... Not sure that is what it is, but might try changing the text box to just "SearchText"

	Lee Shastid OP @Reply 4 months ago
Donald good catch. The Box was called SearchTextBox. I changed the search string to reflect that but that did not change anything.

	Lee Shastid OP @Reply 4 months ago
I stand corrected. Donald you nailed it again. Originally I only changed it in one field but once I changed it all the search fields when i do the Z and search I get blank results. Awesome thank you all for your help as always.

	Richard Rost @Reply 4 months ago
What I'm seeing is you've got two fields that are using the same criteria as far as I can tell, but you have them in different rows, so you're gonna get an 'or' condition. Essentially, what that's working out to be is either of those fields can have that 'or' condition, so there could be a Z in either one of those things.

	Lee Shastid OP @Reply 4 months ago
Thank you Richard. I wanted the OR condition instead of the And condition. I had the Query in my string instead of the search box name. Donald's Idea solved my issue once again. Great help here from all. Thank you to everyone.

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

Next Unseen

New Feature: Comment Live View