Query with multiple partial matches

[Uvuriel03]

Okay, so here's what I'm going for.

I have an inventory database that has a whooooole lot of part numbers. Every day, we get orders for particular parts.

We create a pull sheet by using a query based on the inventory, and then a report based on that query to make it look nice.

The query originally had 10 parameter inputs for the part numbers. (IE, under the [PART] column, [part 1] or [part 2] or [part 3] or etc etc.)

This works fine, so long as someone types in a PERFECT match. Therein is my problem. Sometimes, because of handwriting or typos, one or two letters or numbers is off.

I found a partial workaround for this. I can use Like "*"&[What Part?]&"*" to bring up all partial matches, but the problem is that I can only use that ONCE or else it brings up ALL of the parts if one is left blank. (IE, if I do Like "*"&[Part 1]&"*"or Like "*"&[Part 2]&"*, and the user only fills in the parameter for part 1 and just hits the enter button for part 2, the entire part list comes up.)

So! I need some help. I don't know if it would be some clever coding that could help to only bring up partials for filled in parameters, or if there's another way to do it.

Thanks!

 

[dkinley]

I think I sorta understand what you are asking ....

Per your example of: Like "*"&[Part 1]&"*"or Like "*"&[Part 2]&"* try using something like the following ...

If you are using the QBE on the first criteria row under 'Part 1' use Like "*"&[Part 1]&"*" and under 'Part 1' use Is Null.

On the second criteria row under 'Part 1' use Is Null and under 'Part 1' use Like "*"&[Part 2]&"*".

-dK

 

[Uvuriel03]

I'm not sure I followed that...

Part 1 is one of the parameter query values I use. As in, that's the question that pops up in the parameter box.

[Part] is one of the column names.

 

[dkinley]

Apologies for not getting back to you sooner.

Could you post a copy of your db by chance? Or make a copy and remove everything except the parts you are trying to get working?

-dK

 

[Uvuriel03]

That's ok--Actually I ended up integrating this with another function I found and it works out great.

Here's a link to a question I had about that, and how I ended up solving it:

http://www.access-programmers.co.uk/forums/showthread.php?t=159405

 

[dkinley]

Great!

Thanks so much for posting your final resolution, too!

-dK