Results 1 to 7 of 7
  1. #1
    vickan240sx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    34

    Run-time Error '3075' - Syntax error (missing operator) in query expression

    Hi,

    I've been stuck on this error for the last 2 days and I can't seem to figure out what it is that is wrong.



    On my initial form, I've used the record source as SELECT * FROM [Legal Files];
    However, when doing this, I had to manually write this code in. When I tried to add this code by using the ... I was unable to do so because I would get a "The SELECT statement includes a reserved word or an argument name that is misspelled or missing".
    But, by manually writing in that code, my form was able to pick up all the record information correctly.

    On my search form, I have this expression for my search button.

    Code:
     Private Sub cmdSearch_Click()
    
        If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
            MsgBox "You must select a field to search."
            
        ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
            MsgBox "You must enter a search string."
            
        Else
        
            'Generate search criteria
            GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
            
            'Filter frmCustomers based on search criteria
            Form_FileLookup.RecordSource = "SELECT * FROM [Legal Files] where " & GCriteria
            Form_FileLookup.Caption = "[Legal Files] (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
            
            'Close frmSearch
            DoCmd.Close acForm, "Search"
            
            MsgBox "Results have been filtered."
            
        End If
        
    End Sub
    When running the search, I get the syntax error. When debugging, the following code is highlighted.

    Code:
     Form_FileLookup.RecordSource = "SELECT * FROM [Legal Files] where " & GCriteria
    I'm taking this code from a sample template online where the code works, but I can't get it to work on my own database.

    If somebody could help me, that would be greatly appreciated.

    Thank you.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In the interests of learning how to fish, try this technique to see what the final SQL looks like:

    http://www.baldyweb.com/ImmediateWindow.htm

    I suspect I know the problem, so if you don't spot it post the SQL here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    vickan240sx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    34
    Quote Originally Posted by pbaldy View Post
    In the interests of learning how to fish, try this technique to see what the final SQL looks like:

    http://www.baldyweb.com/ImmediateWindow.htm

    I suspect I know the problem, so if you don't spot it post the SQL here.
    Hi,

    I input that debut.print code and the following was printed out on the immediate.

    Code:
     SELECT * FROM [Legal Files] where Loan Number LIKE '*503031*'

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As I suspected, you have an inadvisable space in the field name. In the same way you bracketed the table name, you'll need to add to your code so the field name ends up with brackets around it. Or better yet get rid of the spaces.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    vickan240sx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    34
    Do you think it's because my txtSearchString words have spaces in them?

  6. #6
    vickan240sx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    34
    OMG, I go it to work .

    Here is the final code that I input into VBA.

    Code:
     Private Sub cmdSearch_Click()
    
        If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
            MsgBox "You must select a field to search."
            
        ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
            MsgBox "You must enter a search string."
            
        Else
        
            'Generate search criteria
            GCriteria = "[" & cboSearchField.Value & "]" & " LIKE '*" & txtSearchString & "*'"
            
            'Filter frmCustomers based on search criteria
                    
            Form_FileLookup.RecordSource = "SELECT * FROM LegalFiles where " & GCriteria
            
                         
            Form_FileLookup.Caption = "LegalFiles (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
            
            'Close frmSearch
            DoCmd.Close acForm, "Search"
            
            MsgBox "Results have been filtered."
            
        End If
        
    End Sub
    Thank you very much for your help!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Microscopically more efficient, and to me easier to read:


    GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2013, 12:59 PM
  2. Replies: 5
    Last Post: 03-27-2012, 01:40 PM
  3. Replies: 3
    Last Post: 08-19-2011, 09:06 AM
  4. Syntax error missing operator(3075)
    By parisienne in forum Programming
    Replies: 1
    Last Post: 04-07-2011, 02:29 PM
  5. Replies: 1
    Last Post: 10-07-2009, 07:36 AM

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