Best match

[ales]

In table A I have data like
Field1
1234
12345
123
2122
1

and
in Table B is list of values and names like
Field2 Field3
1 aaa
123 bbb
1234 ccc
2 ddd

Result in query should be the following
Field4 Field5
1234 ccc
12345 ccc
123 bbb
2122 ddd
1 aaa

Below is an excel function, which solve the example.

Function BestMatch(What As Range, InRng As Range, ColNo As Variant) As Variant
Dim WF As WorksheetFunction
Dim Rng As Range
Dim x As Long
Dim i As Integer
Set WF = WorksheetFunction
Set Rng = InRng.Resize(, 1)
On Error Resume Next
x = WF.Match(What, Rng, 0)
If Err = 0 Then
BestMatch = WF.Index(InRng, x, ColNo)
Else
For i = Len(What) - 1 To 1 Step -1
Err.Clear
If IsNumeric(Left(What, i)) Then
x = WF.Match(Val(Left(What, i)), Rng, 0)
Else
x = WF.Match(Left(What, i), Rng, 0)
End If
If Err = 0 Then
BestMatch = WF.Index(InRng, x, ColNo)
Exit For
End If
Next i
End If
End Function

Now I wonder how to do this in access, becouse I have more than 65.000 rows of data.

Any help would be appreciate.

Best regards, Ales