Wildcard in jet SQL statement 2

[cdgeer]

I have a quick syntax question. In the SQL below, how would I include a wildcard with the [FirstName] so that if the FirstName has a middle initial after it, it will still be included in the query result.
For example:
( Anthony Susan = Anthony Susan B )

I know that * is the wildcard, just don’t know how to include it in the statement.

SELECT All_Personnel.LastName, All_Personnel.FirstName, All_Personnel.Active, All_Personnel.EEID, All_Personnel.JobTitle, All_Personnel.Department, All_Personnel.RollUp, All_Personnel.Supervisor, All_Personnel.SupervisorEEID, All_Personnel.StartDate, All_Personnel.TermDate, All_Personnel.PersonnelType
FROM All_Personnel
WHERE (((All_Personnel.LastName) In (SELECT [LastName] FROM [All_Personnel] As Tmp GROUP BY [LastName],[FirstName] HAVING Count(*)>1 And [FirstName] = [All_Personnel].[FirstName])))
ORDER BY All_Personnel.LastName, All_Personnel.FirstName;

 

[MajP]

you could build this function or similar

Public Function firstOnly(varName As Variant) As String
  Dim strName As Variant
  Dim strNames() As String
  Dim i As Integer
  If Not IsNull(varName) Then
    strNames = Split(varName, " ")
    For i = LBound(strNames) To UBound(strNames)
       strName = Replace(strNames(i), ".", "")
       strName = Trim(strName)
       Debug.Print strName
       If Len(strName) > 1 Then
         firstOnly = firstOnly & " " & strName
       End If
    Next i
    firstOnly = Trim(firstOnly)
  End If
End Function

If I pass the above function
Susan B
or
Susan B.
it returns Susan

It also handles
Mary Sue B.
Mary Sue
and returns Mary Sue

so then you could do something like

[firstName] = firstOnly([All_Personnel].[FirstName])

 

[PHV]

Replace this:
[FirstName] = [All_Personnel].[FirstName]
with this:
[FirstName] Like [All_Personnel].[FirstName] & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

The reason I did not do what PHV suggested was several exceptions such as

Mary would then match
Mary Beth
Marylyn

Joe would then match
Joel

even if you changed it to
[FirstName] Like [All_Personnel].[FirstName] & ' *'
You would avoid
Joe and Joel
But still match Mary and Mary Beth S.

 

[cdgeer]

Thanks to both of you. The VB code is a little above me but I will try to figure out where to put in the table name and field names.

The "Like [All_Personnel].[FirstName] & '*' " thing still misses "Smith Glen R" when compared to "Smith Glen".

Can't understand why with the wildcard there.

 

[PHV]

And this ?
[FirstName] Like Trim([All_Personnel].[FirstName]) & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Just drop the function into a standard module, and that is it. In the Sql just wrap the first name such as

And [FirstName] = [All_Personnel].[FirstName]
to
And firstOnly([FirstName]) = firstOnly([All_Personnel].[FirstName])

I still believe this catches more correctly then what is being proposed.

 

[PHV]

I still believe this catches more correctly
I agree, this is the pro.
The con is that you can't use an UDF in a recordset.