Search Button to find any part of field

[Guest]

I have a search form that the user cann search based on one of three things:
FPI
Coordinator
SR
With a search button that opens the field. Below is the code I am using:

Private Sub cmdSearchFPI_Click()
Dim strSQL As String
Dim strWhere As String

strForm = "main"
If Not IsNull(txtFPI) Then
strWhere = strWhere & " AND [fpi]= " & "'" & txtFPI & "'"
End If

If Not IsNull(txtSR) Then
strWhere = strWhere & " AND [sr] = " & "'" & txtSR & "'"
End If

If Not IsNull(txtCoordinator) Then
strWhere = strWhere & " AND [coordinator] = " & "'" & txtCoordinator
& "'"
End If

If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If

'open report based on the contents of strSQL
DoCmd.OpenForm strForm, acNormal, , strSQL

End Sub

I was wondering if/where you can specify so that it will open using any part
of field? For example currently, if you put user1 in coordinator field and
push the search button it will not find it because it wants the exact name of
user111.

Any help would be appreciated.

Thanks

 

[Douglas J Steele]

If you want user1 to return user1, user12 and user123, use:

If Not IsNull(txtCoordinator) Then
strWhere = strWhere & " AND [coordinator] LIKE '" & txtCoordinator &
"*'"
End If

If you also want to pick up abuser1234, use:


If Not IsNull(txtCoordinator) Then
strWhere = strWhere & " AND [coordinator] LIKE '*" & txtCoordinator &
"*'"
End If

 

[Jeff Boyce]

Check into using the "Like" qualifier with wildcard symbols. In query
design mode, the select criterion would look similar to:

Like [enter search term] & "*"

In your example, entering "user1" would return
user1
user11
user12345
...
(anything starting with "user1")

Regards

Jeff Boyce
Microsoft Office/Access MVP