Query that returns records with partial match

[Colman]

I understand that a query can accept wildcards in the search criteria but this is different.

I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".

I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.

In other words, the record entries themselves need to be treated like wildcard entries.

Not sure if this should be simple or if it defies some fundamental law of physics, but I can't get my head around it.

Any help appreciated.

 

[namliam]

Normal construct would be:
Where Field like Combobox & "*"
in this case you want to turn it around
Where Combobox like Field & "*"

or perhaps something along the lines of
Where Left(combobox, len(field)) = field

Seems like an odd requirement, but I hope this helps you along.

 

[Galaxiom]

I think you would need to provide a bigger list of examples of what matches and what doesn't before I would try it.

 

[Colman]

Thanks. I think namliam has it, I will try it later.

It might be odd but this is what its about.

Database has a table of 'change' records, each detailing a change to a product.

This table has a related table of 'product' part numbers so that each 'change' record gets set up with a number of 'product' records, representing the part numbers of the products that are affected by the change. e.g. "12345-V001", "12345-V002" etc

The form I described previously is for a final inspection operation where the inspector selects a product e.g. 12345-V001, and wants to see any change records that relate to it.

This currently works well, but sometimes a product has a few dozen variants, all of which may be affected by a single change. Where this is the case, instead of having to list every variant as related 'product' records for the change, I would like to be able to have a single 'product' record like"12345-V", which would result in that change being flagged up to the inspector whenever any of the variants is being inspected. e.g. "12345-V001" or "12345-V002".

 

[jdraw]

I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.

Sounds like this not normalized _but we don't know the business.
More examples please.

 

[Colman]

I should explain that product part numbers come with various prefixes, not just "12345"

 

[jdraw]

What I am requesting is a few more examples or explanation, so readers can see exactly what you might do to
a) select "12345-V1" and
b) exclude "12345-V2"

Seems to me you would need all 8 chars, but more examples may help....

 

[Colman]

Thanks all but namliam's suggestion did the trick.

I've never defined a query using the SQL view before but using namliam's logic I just adjusted what was there and it makes sense and it works.

If anyone is still curious, this is what I was after.

User enters product part number "12345-V001"

Query returns records with any of the following entries;
"12345-V"
"12345-V0"
"12345-V00"
"12345-V001"

Query doesn't return records with any of the following entries;
"12345-V1"
"12345-V01"
"12345-V002"

So the characters that are present in the records must match the left part of the specified product part number, but they don't all have to be present.

I'm sure there's an easier way to explain this so apologies from me and thanks to namliam.