Query to return records with partial match

[Gkirkup]

I have a query used to drive a subform showing part numbers. It works fine, but I need the ability to enter a partial part number and have it return anything starting with that partial part number.
So I enter "ABCDE" and the query returns "ABCDE123", "ABCDE345", "ABCDE456", etc.
I imagine that I need to do a match on LEFT(PartNumber,LEN(MyEntry)), but I would like to do this without the criteria being a control on a form, as I can't get that to work. So can I do this using the query as the data source of the subform, then a Master / Child relationship from my main form to the subform?

Robert

 

[MarkK]

How do you specify the criteria without typing them into a control on a form? I think you have no choice but to get it to work that way.
And you may have an additional problem. Are the PartNumbers on the subform children of the object in the main form? If so, do you want your subform search to only return children of the object in the main form?

 

[Gkirkup]

Lagbolt: You are probably correct. I am having problems with the criteria of the query being an unbound control on the form, so was looking for any alternative.

Robert

 

[boblarson]

The query's criteria on that field can be:

Like "*" & [Forms]![YourMainFormName]![ControlName] & "*"

and that should be able to filter by your text box on your main form.

 

[Gkirkup]

Bob: Hey, my subform woke up! So I am getting there. I can enter data into the control and get the correct partial matches returned to the subform by the query.
Now my problem is that it loads all 300,000 records at startup. Possibly because the control on my main form is not being populated early enough (from another form), and it's doing a 'like' on a blank control. When should I populate the control on my main form?

Robert

 

[boblarson]

Well, subforms load BEFORE the main form, so you really should set the initial value using some method in the subform's load event.

 

[Gkirkup]

Bob: Subforms load BEFORE the main form? I would never have thought of that one. You can learn something new every day - it now works perfectly.
It's so great to have people like Bob Larson on this forum. Unless you are the ACTUAL Bob Larson of course....

Robert

 

[boblarson]

It's so great to have people like Bob Larson on this forum. Unless you are the ACTUAL Bob Larson of course....

I am he (I fear I just cannot keep from posting).

 

[Gkirkup]

Bob: Although the 'Like' query works great, it may not always be what is wanted. I think I may need to put two buttons on the form for 'Search from start of part number' and 'Search any part of part number'.
Is there something that works just like 'Like' in a query, but matches only from the left of the field, not anywhere in the field?
I probably need to have two queries and somehow switch between the two.

Robert

 

[boblarson]

Remove the first "*"

And leave only the trailing one.