Results 1 to 2 of 2
  1. #1
    jacie is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    1

    RunTime Error 3075, code for search button

    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!

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    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:

    Code:
    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:

    Code:
    [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".

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax error missing operator(3075)
    By parisienne in forum Programming
    Replies: 1
    Last Post: 04-07-2011, 02:29 PM
  2. Runtime error in preventing duplicates code
    By emilyrogers in forum Access
    Replies: 10
    Last Post: 10-07-2010, 08:14 AM
  3. Error 3075 when trying to preview report
    By natalie in forum Forms
    Replies: 2
    Last Post: 08-03-2010, 12:15 PM
  4. Runtime 3075 error
    By whm1 in forum Programming
    Replies: 4
    Last Post: 03-24-2010, 02:50 PM
  5. Error 3075 Missing Operator
    By KLynch0803 in forum Queries
    Replies: 5
    Last Post: 02-11-2010, 01:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums