Find the closest Matched Record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mattc66 - 15 Mar 2007 00:24 GMT

Wild card look up's. I want the user to be able to enter a number or part of
a number and the system to find the first match or if it can't find a match
to find the next one that is close.

Can this be done. Below is my current code that works great if you have the
exact number.

Thanks
Matt

Private Sub LookUpSerialNo_AfterUpdate()
' Find the record that matches the control.
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpSerialNo.Value
stLinkCriteria = "[SerialNum]=" & "'" & SID & "'"

'Check table for for item number.
If DCount("SerialNum", "Location", stLinkCriteria) >= 1 Then
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

End If
Set rsc = Nothing
End Sub

Signature

Matt Campbell
mattc (at) saunatec [dot] com

Reply to this Message

Allen Browne - 15 Mar 2007 03:24 GMT

With numeric fields, you can identify the nearest match by sorting on the
smallest absolute difference, e.g.:
ELookup("SerialNum", "Location",, "Abs([SerialNum] - " & [Text99] & ")
DESC")
where ELookup() is:
http://allenbrowne.com/ser-42.html

With Text fields, you will need to be clear in your own mind about what
"neartest match" means. Val() would work if the text is all digits. Text
matching might work if the field is always a fixed number of characters, or
you may need to add leading or trailing spaces if it is not.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Wild card look up's. I want the user to be able to enter a number or part
> of
[quoted text clipped - 29 lines]
> Set rsc = Nothing
> End Sub

Reply to this Message