Can combo box look-up a value by searching right to left? (1 Viewer)

[GinnySimon]

Typically, when using a combo box as a look-up, the search starts from the left to the right.

For example, if I type "123" in the combo box will first start looking for "1", and then "2" , and then "3" until it settles on "123". I need it to search from right to left? For example, to search for the "3", and then the "2", and then the "1" and then settle on "123". Is that possible?

I have samples and sometimes the ID is "00123" and sometimes the ID is "123", but there's no way to no ahead of time if the ID is "00123", or "123" or, even "000123". So it would be super helpful when I typed in "123" and see the choice "00123".

Maybe it would be better to use a wild card? So that when I searched for "123", "00123" would come up?

Any help is greatly appreciated! Thank you.

 

[jdraw]

You could try where ID Like "*" & "123".

More info on what you are searching for would be helpful.

 

[theDBguy]

Hi. Welcome to AWF!

Since you want to modify the natural behavior of the control, you would need to use code to program the new behavior yourself. Is that acceptable?

 

[GinnySimon]

Hi. Welcome to AWF!

Since you want to modify the natural behavior of the control, you would need to use code to program the new behavior yourself. Is that acceptable?

Yes, that would be fine. Thanks.

 

[GinnySimon]

You could try where ID Like "*" & "123".

More info on what you are searching for would be helpful.

The lookup combo box uses a table with 2 fields: Master_ID and Person_ID
The user will enter the Person_ID into a combo box and then I move the form to the appropriate record. The combo box has a long list of ID. For example:

Master_ID Person_ID
1001 1234
2121 00788
2121 0788
3003 9874

* 00788 and 0788 are the same subject

The problem is that a person can have many different Person_IDs and only 1 Master_ID. Sometimes leading zeroes where used and sometimes they weren't used. So when the lab person has a sample in their hand that is connected to Person_ID "788" they go to the look-up control and type in "788" and since there is no "788" nothing is returned. But in reality "788" is the same person as "00788" and the same person as "0788". The lab person knows this and can then try and look-up "0788" and find the person, but it would be much better if the lab person could enter "788" and both "00788" and "0788" were returned.

One time I (not in Access) that looked up from right to left, so it would look up the 8, then 8, then 7, and that way 0078 and 00788 would still be in the drop-down list. Maybe that's not possible in Access. Maybe I have to use a wild card?

I really appreciate your help!

 

[isladogs]

No need to search from R to L.
Add a field to your combo that strips leading zeroes from the values and use that for filtering data
E.g. Use CInt(Person_ID)

 

[CJ_London]

in your combo change event put something like

combo.rowsource="SELECT ID, PersonID FROM myTable WHERE PersonID LIKE '*" & combo.text & "'"

But would suggest, easier to use a textbox and in the textbox afterupdate event, or a separate button

Or even easier, in your combo rowsource display a calculated value something like

SELECT ID,val(personID) FROM myTable

 

[Cronk]

I'd have the row source of the unbound combo being "SELECT Master_ID, PersonID FROM myTable" with 2 columns, the first being Master_ID and hidden (ie column width 0)

In the combo's AfterUpdate event, set the form's filter to the combo value
me.filter = "Master_ID=" & me.comboName

 

[MajP]

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk

If you do an FAYT it will find from front, back, or middle.

 

[arnelgp]

see this demo.