RunTime Error 3075, code for search button

[jacie]

so im modifying some code from a template to make a search button for my form, and its giving me the following error:


Run time error 3075 Syntax error (missing operator) in query expression '[FIRST NAME] LIKE "joe*" AND'.

the code is below, the bold italic line is the one hilighted when i hit "debug" after the error message. (i entered "joe" in the first name field on the entry form)


Option Compare Database
Private Sub SearchStudent_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

'initialize to null
varWhere = Null

'build filter
If Not IsNull(Me.FirstName) Then
varWhere = varWhere & "[FIRST NAME] LIKE """ & Me.FirstName & "*"" AND "
End If

'check to see that there is a filter
If IsNull(varWhere) Then
MsgBox "you must enter at least one search critera.", vbInformation, gsrApptitle
Exit Sub
End If

'open recorset to see if anything matches
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM qry_student_data WHERE " & varWhere)
'if none found
If rst.RecordCount = 0 Then
MsgBox "No students match your critera.", vbInformation, gstrAppTitile
'clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

'stuff to fix focus issues
Me.Visible = False
rst.MoveLast

'show summary
DoCmd.OpenForm "frm_student", WhereCondition:=varWhere
Form!frm_student.SetFocus

'done, close recordset
DoCmd.Close acForm, Me.Name
rst.Close
Set rst = Nothing
End Sub


im not sure what im doing wrong as i copied this from a template that was working, and I just changed the names of the fields and text boxes and such. any help would be great, thanks!

[ajetrumpet]

first of all, varWhere should never equal NULL if it's a string. set it to "" (0 length string) to start with. that's a problem.

next, this:

varWhere = varWhere & "[FIRST NAME] LIKE """ & Me.FirstName & "*"" AND "

is complicated to read for anyone. what people do in scenarios like this is use the DEBUG.PRINT option to test the clause and get it to where it needs to be before executing it in a procedure. so here, do that until it looks like this in the immediate window:

[FIRST NAME] LIKE '*' & Me.FirstName & '*' AND

(that ends in a space, but you can't see it)

so when you manipulate it and end up with the above, it will read correctly. hence, you will be able to concat it into the rest of the sql without error. the way I usually do this kind of testing is in the immediate window itself. when you test expressions in that window directly, you have to preceed the expression with a "?" mark. The ? is the same thing as saying "return".