Pretty new to access and trying to get a query to work.

[leprkon]

Hello and greetings!

I am working on a query for an inventory table so that I can search for products.

I made a search form with multiple fields and made a search query that links to my Inventory List table.


For the Criteria, I put Like "*" & [Forms]![Search Form]![Medium/Reagent Name] & "*".

However, when I try to run the query from the search form, the query comes up empty. I am not sure if I am doing something wrong or not. If I could get some help, that would be awesome.

Thanks ahead of time!

[June7]

Is Medium/Reagent Name the name of a control on the form? Is it a combobox? Is the combobox multi-column? Is the actual value of combobox an ID and not a descriptive name?

[leprkon]

Is Medium/Reagent Name the name of a control on the form? Is it a combobox? Is the combobox multi-column? Is the actual value of combobox an ID and not a descriptive name?

Medium/Reagent Name is the name of the unbound text box that I put down. Like I said, I am still pretty new to access, so I am not sure if I used the correct method or not for this.

[June7]

Seems the criteria should work. Post the complete query SQL statement for review.

If you want to provide db for analysis, follow instructions at bottom of my post.

[leprkon]

Seems the criteria should work. Post the complete query SQL statement for review.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks again, here is the file. I hope I followed your instructions correctly.

Inventory Sheet.zip

[June7]

In my experience, LIKE operator and wildcard won't work for numbers and dates. Remove those criteria and then try the search.

Review http://www.allenbrowne.com/ser-62.html

[leprkon]

In my experience, LIKE operator and wildcard won't work for numbers and dates. Remove those criteria and then try the search.

Review http://www.allenbrowne.com/ser-62.html

Thanks for the help and quick reply. I removed the LIKE operator and wildcard from all criteria and I am still getting no results from the search. Is there another approach that would give me the same results as if I were using the LIKE operator and wildcard search?
**Edit** I am reading through the link you posted, and a portion of it is going over my head. haha. I'll attempt to read it again and see if I can get any results on my own. Thanks again!

[June7]

I meant remove the criteria from only the date type fields. Then the search works. However, I tested by entering a value into only the Medium/Reagent Name control.

[leprkon]

I meant remove the criteria from only the date type fields. Then the search works. However, I tested by entering a value into only the Medium/Reagent Name control.

Ah, I thought I had tried that. I'll try again in a bit when I'm on my airplane and I'll update you when I get home. Thanks again for the help. I really appreciate it.

[leprkon]

Sorry about the double post.

I just wanted to update and say that I tried removing the criteria from only the data type fields and still had no luck with retrieving any search results.

[Rawb]

You need to write the Query so that, after everything is put together, the text string is wrapped in quotes. The easiest way to do this, since you're already using double quotes, is to add single quotes like so:

Like "'*" & [Forms]![Search Form]![Medium/Reagent Name] & "*'"

[leprkon]

You need to write the Query so that, after everything is put together, the text string is wrapped in quotes. The easiest way to do this, since you're already using double quotes, is to add single quotes like so:

Like "'*" & [Forms]![Search Form]![Medium/Reagent Name] & "*'"

Thanks for the help! I put the criteria in, but I am still coming up with zero results. On another note, f I am removing LIKE and wildcard from the other criteria, would I receive results if I leave the corresponding fields blank when performing my searches?

[June7]

You meant the date type fields?

Doesn't matter if textboxes have inputs if they are not referenced by the query.

Here is what I did:

1. downloaded your db

2. remove criteria from the date/time type fields in the Search Query

3. open Search Form and enter SDA into Medium/Reagent Name

4. I forgot to mention, also remove criteria from [Amount Left] because there is no data in this field for any records. This technique works best if data is in all of the criteria fields for every record. This is because Null will be rejected. An alternative criteria is:

Like "*" & [Forms]![Search Form]![Amount Left] & "*" Or Is Null

Note that the apostrophes are not required - Access will manage that in query object. Building SQL statements in VBA would require the apostrophes.

[leprkon]

You meant the date type fields?

Doesn't matter if textboxes have inputs if they are not referenced by the query.

Here is what I did:

1. downloaded your db

2. remove criteria from the date/time type fields in the Search Query

3. open Search Form and enter SDA into Medium/Reagent Name

4. I forgot to mention, also remove criteria from [Amount Left] because there is no data in this field for any records. This technique works best if data is in all of the criteria fields for every record. This is because Null will be rejected. An alternative criteria is:

Like "*" & [Forms]![Search Form]![Amount Left] & "*" Or Is Null

Note that the apostrophes are not required - Access will manage that in query object. Building SQL statements in VBA would require the apostrophes.

Oops, Yes. I meant Date.

I am still getting the same problem.

The only way I got the search to work (with Medium/Reagent Name) was to have each criteria separated by OR instead of AND. Should it be working with AND?

Sorry again. I am a total newbie at this.

***EDIT***
I forgot to mention that if I separate Medium/Reagent Name with OR from the rest of the criteria, then I am only able to make searches in Medium/Reagent Name. If I attempt a search for another field, the query brings back the entire inventory list.

[June7]

It should work with AND. Did you follow those steps exactly?

I tried multiple criteria and it still works.

Medium/Reagent Name: SDA
Lot Number: 4199901

Returns 5 records.