Find the closest Matched Record

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

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
MattPrivate 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
 
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

mattc66 via AccessMonster.com said:
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
MattPrivate 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
Click to expand...
 
Back
Top