Basic Query with wildcard giving problems

[carrod65]

Hello,

I am a noob access user. I have spent about an hour and a half using search in these forums and google and haven't been able to resolve my issue, so wanted to check with the experts to see if they can point me in correct direction.



I have a big table with product numbers listed along with inventory levels and other information. I am trying to write a basic query to enter a product number and pull a report up based on that query and show inventory etc.

I think the issue I am having is that product numbers don't have any specific format and have great variability in their length and characters used. I posted some examples below.

I am trying to write a query to have the "base number" get entered and then pull up a list of similar products.

The query I am using is: Like [enter_Material] & "*"


When the prompt comes up, I type in "B123456" and hope to get all results that start with that number. For some reason, the one with -BULK does not appear in my query results.

Thank you for looking at this, any suggestions are greatly appreciated.

An example of what the product numbers look like:

B123456-BULK

B123456-10L

B123456-5L

B123456-1L

B123456-15L

B123456-200L

A654321-BULK

A654321-500G

A654321-1KG

A654321-2.5KG

A654321-5KG

A654321-12KG

A654321-20KG

A654321-50KG

A234256

D1234-U

325067

P65124-BULK-F

[ItsMe]

I would not expect that behavior. What happens when you type less characters, like B123?

[carrod65]

Further shortening of my search input seems to have the same result. B123 is still unable to pull up B123456-BULK but includes every other instance of B123456 above.

All of the following inputs come up with the same list(I tried them with and without the asterisk even though it should be built into the query):

B123
B123*
B1234
B1234*
B12345
B12345*
B123456
B123456*

All searches return only these:

B123456-10L

B123456-5L

B123456-1L

B123456-15L

B123456-200L

But not
B123456-BULK


Could it have anything to do how my material number is formatted in the background table?

Thanks again for looking at it.

[ItsMe]

I am not sure what you mean by formatted. As soon as you use the LIKE operator it will be looking for text. However, this will still work to search number data types. The query considers it to be text.

Having said that, all of the data you are posting here is text. Are you using an alias in your query object? Are these results concatenated from multiple fields?

[carrod65]

I am not sure what you mean by formatted. As soon as you use the LIKE operator it will be looking for text. However, this will still work to search number data types. The query considers it to be text.

Having said that, all of the data you are posting here is text. Are you using an alias in your query object? Are these results concatenated from multiple fields?

Yes thank you very much! Your question "Are these results concatenated from multiple fields" put me in the right direction.

There is no concatenated fields, but I think the relationship between my two tables is the issue. I created a new query identical to my previous one, and it seems to be working.

I'm not entirely sure why, but it seems to be working now, so thank you very much, I'm going to mark it as solved.

[ItsMe]

Glad to hear!

[carrod65]

I had two tables, the only relationship being the material number between them. Even though my query was only looking for the main table, it was omitting records that weren't present in the linked table.

The second table doesn't have the -BULK product numbers, so this was somehow the culprit. I double checked that my query was only searching fields in the first table, but it was somehow looking at the second table and axing them from my report.

You guys are the best on these forums, wish I knew a bit more so I could help.