Find only specific part of a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. Please can someone tell me if it is possible to only search for "a piece"
e.g the first 5 letters in a record?
Basically on my After_Update action if there is a record named "christopher"
and the name "chris" is entered. I need the code to search for all records
containing "chris" as part of the text.
Any help would be appreciated.
Thanks
Here's the code I have done at the moment, it only returns exact matches.
"SELECT Name FROM table WHERE name = '" the name entered on the form "'"
 
Use DLookup() to see if there is a similar record.
Use Left() or Mid() to parse out the 5 characters from the entry.
Use Like to match part of the field.

This kind of thing:

Private Sub Text0_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant

With Me.Text0
If .Value = .OldValue Or IsNull(.Value) Then
'do nothing
Else
strWhere = "[Name] Like ""*" & Left(.Value, 5) & "*"""
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Possible duplicate of record " & varResult
End If
End If
End With
End Sub

BTW, hopefully you don't really have a field called Name. Lots of things
(such as forms) have a Name property, and the ambiguity will bite you at
some point when Access misinterpts your code.
 
Use Like in the SQL combine with a * to define which part

SELECT Name FROM table WHERE name Like [Please Enter a Name:] & "*"

Will display all the records that the name you entered start with
==============================================
SELECT Name FROM table WHERE name Like [Please Enter a Name:] & "*"
Will display all the records that the name you entered End with

==============================================
SELECT Name FROM table WHERE name Like "*" & [Please Enter a Name:] & "*"
Will display all the records that the name you entered is in any part of it
 
Thanks guys. The first explanation was exactly what I was looking for. I
needed to check for duplicate users. Spot on.
Oh and don't worry, I didn't name the actual field "name".
Thanks again. Much appreciated.
 
Back
Top