I have a MS Access table called File
It has fields called FileNum, Description and Comments. I want to enable the end user to type in one or more words / numbers to search for a particular file.

My VB code looks like:

gstrSQL = "SELECT * FROM PENDING WHERE"
'if the Search File Name Check box is selected ...
If bFileName Then
gstrSQL = gstrSQL & " FILENUM LIKE '*" & strSearchText & "*'"
End If

As a test, I'm asking the program to search for "1110" in the file number field. I have a file number in the table with the value "1110-4-02" but the search comes back empty.
??

Please advise.
Thanks.

Try %.

Do you mean before the variable name insert a % sign?

gstrSQL = gstrSQL & " FILENUM LIKE '%" & strSearchText & "%'"

YIPEE!
Thanks a bunch. That worked really well. One other question.
I have two tables:
one called Users and one called Resources. All users in the Users table are represented in the Resources table.
I'm trying to select all users from Users and then select additional information for each of them in the Resources table.
How do I add to my recordset?
Code:

gstrSQL = "Select * from Users where "
If Connect Then
'which parms are available?
If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "'"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "'"
End If
Set oRS = goConn.Execute(gstrSQL)
'oRS.LockType = adLockBatchOptimistic

If Not oRS.EOF Then
'get email & telephone information from resource table.
Dim objTemp As ADODB.Recordset, i As Integer
Set objTemp = New ADODB.Recordset

For i = 0 To oRS.RecordCount - 1
gstrSQL = "Select email, Telephone, Ext from Resource Where FName='" & oRS.Fields(2).Value & "' AND LName='" & oRS.Fields(3).Value & "'"
Set objTemp = goConn.Execute(gstrSQL)
' add to end result set
Next
oRS.MoveFirst
oRS.AddNew "email", objTemp.Fields(0).Value
oRS.AddNew "Telephone", objTemp.Fields(1).Value
oRS.AddNew "ext", objTemp.Fields(2).Value

Set objTemp = Nothing

GetUserProfile = True
End If
Else
mstrError = "Database currently not available. Retry later"
GetUserProfile = False
End If
Exit Function

When i do the above, I get an error that says "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." When I uncomment the line about the lock type, I get the error "Operation is not allowed when the object is open."

Moving the lock type statement above "Set oRS = goConn.Execute(gstrSQL)" gives the error:
Object variable or With block variable not set

Why not have one select statement that joins users to resources? You benefit because you only make one connect and one pass through the data, getting the information you want in one result set. Also, your errors may go away.

SELECT something FROM users u, resources r
WHERE u.FName = r.FName AND u.LName = r.LName