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

-- 
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.accessmonster.com

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mattc66 via AccessMonster.com" <u16013@uwe> wrote in message
news:6f30a29da0ff4@uwe...
> 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
>
> -- 
> Matt Campbell
> mattc (at) saunatec [dot] com
>
> Message posted via http://www.accessmonster.com
>