Searching LIKE Numbers

[NEWER USER]

I have an Unbound text box on a form that searches my database for exact
matches. On After Update Event, it moves the focus to NumberID and if a
match is found, displays the record on the form. If no match, Microsoft
Access displays a message stating that "You can not go to the specified
record". If No record is found, I would like to open another form and
display the CLOSEST MATCH(s). For instance, I search 1234ABC and it does not
exist. However, 1234A does exist. I want to show this record as a possible
choice. This where I need some help. So far I have the following code and it
works very quick when searching. Any help appreciated.

Private Sub Search_AfterUpdate()
On Error GoTo Search_AfterUpdate_Err
With CodeContextObject
Me!Search = Replace(Me!Search, " ", "")
Me!Search = Replace(Me!Search, "-", "")
Me!Search = Replace(Me!Search, ".", "")
Me!Search = Replace(Me!Search, "/", "")
DoCmd.GoToControl "NumberID"
DoCmd.FindRecord .Search, acEntire, False, , False, , True
End With

Search_AfterUpdate_Exit:
Exit Sub

Search_AfterUpdate_Err:
MsgBox Err.Description
Resume Search_AfterUpdate_Exit

End Sub

 

[TonyT]

Your problem as I see it is deciding what is a close match;

would you say 1234ABD is a closer match than 1234A or 1235ABC

are all the records in the same format, ie. 4 digits up to 3 characters?

probably the *best* solution will be to use the LIKE predicate in
conjunction with Left() function, but that really depends on number / string
format.

hth

TonyT..

 

[roger]

I'm working on somthing similar, and my new strategy is going to to be
removing one character at a time and searching for each sub-string.

So in a loop you would search for 1234ABC, then 1234AB, then 1234A, then
1234, then 123, etc.

or maybe you want to trim them off the other end, 234ABC, 34ABC,

(or maybe both?)

Like I said I'm only working on this now. Haven't written any code yet,
maybe we can work togther?