Run Time Error'3075': Syntax Error(Missing Operator) in query expression-VBForums
Results 1 to 9 of 9

Thread: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2009
    Posts
    28

    Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    I am running a database using MS Access 2000 along with Visual Basic.

    Inside the database I am running a specific tracking summary form that takes information from a populated main table.

    Inside the tracking summary form I have three combo boxes; Status, Fiscal Year and Province, with their respective choices. When a user chooses one of the choices within the combo boxes it gives a
    Run-Time Error'3075':
    Sytnax error (missing operator) in query expression
    'tblmain.Status comment tblMain.ProjectTitle'.

    More Information About the Error:
    When I debug the error it highlights the line of code: Me.RecordSource = sql

    I have declared the variables and there is no other errors with the other code only the highlighted line.

    There are also other lines of code stated under the highlighted line of code, they are not highlighted but may or may not be part of the problem, I am unsure of this.
    Me.RecordSource = sql -(highlighted code)
    Me.cboStatus = statusTemp -(not highlighted)
    Me.cboFiscalYear = fiscalYearTemp -(not highlighted)
    Me.cboProvince = provinceTemp -(not highlighted)

    I am not sure what to do with this error. What do you think the problem is and how do you think I can fix this?

  2. #2
    Lively Member KTech's Avatar
    Join Date
    Jun 2008
    Location
    Pittsburgh
    Posts
    117

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    It says the error is in the sql expression which would indicate the sql variable contains the error. can you print the value before the line executes using "debug.print sql"
    Then you can see the value of sql and you should see an error in that. You can also take the sql statement that is produced and copy it into a new query window and try executing it there. It should highlight exactly where the problem is.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2009
    Posts
    28

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    Quote Originally Posted by KTech View Post
    It says the error is in the sql expression which would indicate the sql variable contains the error. can you print the value before the line executes using "debug.print sql"
    Then you can see the value of sql and you should see an error in that. You can also take the sql statement that is produced and copy it into a new query window and try executing it there. It should highlight exactly where the problem is.

    I have declared the sql variable as:
    Dim sql As String

    I get an error when trying to print the value before the line executes.
    Do you think there are any other options to solve the error?

    I have posted the code here for a reference.
    Code:
    Private Sub cboStatus_AfterUpdate()
    Dim statusTemp As String
    Dim fiscalYearTemp As String
    Dim provinceTemp As String
    Dim sql As String
    
    'using temp variables to avoid a bug where MS Access loses
    'the value in Me.cboStatus and Me.cboFiscalYear
    statusTemp = Nz(Me.cboStatus, " ")
    fiscalYearTemp = Nz(Me.cboFiscalYear, " ")
    provinceTemp = Nz(Me.cboProvince, " ")
    
    'changing AmountApproved column to fiscal year budget amount
    If fiscalYearTemp = " " Then
    lblAmount.Caption = "AmountApproved:"
    Me.txtAmount.ControlSource = "AmountApproved"
    Else
    lblAmount.Caption = "Budget" & fiscalYearTemp & ":"
    Me.txtAmount.ControlSource = "Budget"
    End If
    
    'initializing the filter
    Me.Filter = ""
    Me.Filter = "" 'filter needs to be cleared twice as first clear doesn't always work
    Me.FilterOn = False
    
    'MsgBox (fiscalYearTemp & " " & statusTemp)
    
    'filtering on Status
    If statusTemp = "Open" Then
    Me.Filter = "(Status <> 'Closed' and Status <> 'Rejected' and Status <> 'Withdrawn')"
    ElseIf statusTemp = "CA Not Signed" Then
    Me.Filter = "(Status = 'Project Received' or Status = 'Project Approved' " _
        & "or Status = 'CA Finalized' or Status = 'CA Signed By ED')"
    ElseIf statusTemp = "CA Signed" Then
    Me.Filter = "Status = 'CA Signed By Proponent'"
    ElseIf statusTemp = "Rejected" Then
    Me.Filter = "Status = 'Rejected'"
    ElseIf statusTemp = "Withdrawn" Then
    Me.Filter = "Status = 'Withdrawn'"
    End If
    
    'filtering on FiscalYear
    If (fiscalYearTemp <> " ") And (Me.Filter <> "") Then
    Me.Filter = Me.Filter & " and (FiscalYear = '" & fiscalYearTemp & "'" _
        & " or Status = 'Project Received')"
    ElseIf fiscalYearTemp <> " " Then
    Me.Filter = "(FiscalYear = '" & fiscalYearTemp & "' or Status = 'Project Received')"
    End If
    
    'filtering on Province
    If (provinceTemp <> " ") And (Me.Filter <> "") Then
        If cboProvince = "ON, MB, SK, AB, BC, NT & YT" Then
        Me.Filter = Me.Filter & " and (Province = 'ON' or Province = 'MB' or Province = 'SK' " _
            & "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')"
        ElseIf cboProvince = "QC, NU, NB, NS, PE & NL" Then
        Me.Filter = Me.Filter & " and (Province = 'QC' or Province = 'NU' or Province = 'NB' " _
            & "or Province = 'NS' or Province = 'PE' or Province = 'NL')"
        End If
    ElseIf provinceTemp <> " " Then
        If provinceTemp = "ON, MB, SK, AB, BC, NT & YT" Then
        Me.Filter = "(Province = 'ON' or Province = 'MB' or Province = 'SK' " _
            & "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')"
        ElseIf provinceTemp = "QC, NU, NB, NS, PE & NL" Then
        Me.Filter = "(Province = 'QC' or Province = 'NU' or Province = 'NB' " _
            & "or Province = 'NS' or Province = 'PE' or Province = 'NL')"
        End If
    End If
    
    'MsgBox (Me.Filter)
    
    If Me.Filter = "" Then
        sql = "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
            & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " _
            & "tblMain.ProjectTitle " _
            & "FROM tblMain  " _
            & "ORDER BY tblMain.ProjectCode;"
    ElseIf ((fiscalYearTemp = " ") Or (fiscalYearTemp = "")) Then
        sql = "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
            & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " _
            & "tblMain.ProjectTitle " _
            & "FROM tblMain " _
            & "WHERE " & Me.Filter & " " _
            & "ORDER BY tblMain.ProjectCode;"
    Else
        sql = "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
            & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment, " _
            & "tblFiscalBudget.FiscalYear AS FiscalYear, tblFiscalBudget.Budget " _
            & "tblMain.ProjectTitle " _
            & "FROM tblMain " _
            & "LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode " _
            & "WHERE " & Me.Filter & " " _
            & "ORDER BY tblMain.ProjectCode;"
    End If
    
    
    Me.RecordSource = sql    (this is where the highlighted area is)
    Me.cboStatus = statusTemp
    Me.cboFiscalYear = fiscalYearTemp
    Me.cboProvince = provinceTemp
    
    End Sub
    Last edited by si_the_geek; May 11th, 2009 at 01:03 PM. Reason: added Code tags

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,277

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    Welcome to VBForums

    When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

    I have added them to your post above.

    Quote Originally Posted by mcleyn View Post
    I get an error when trying to print the value before the line executes.
    Please clarify that a bit..

    "an error" is virtually useless because there are millions of errors, and we don't know which one you are getting unless you tell us.

    We also don't know what code (if any) you were using to get the error, or where that code was.

  5. #5
    Fanatic Member Psyrus's Avatar
    Join Date
    Jul 2000
    Location
    Gallifrey
    Posts
    602

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    I agree, the problem is within your SQL string. Have you tried hard-coding values for Me.Filter in your IF statement? I think the problem might be in how you are building your WHERE clause.
    Chris

    VB 6.0 Calendar App Video Gamers Group
    Don't forget to rate people if they helped you.

  6. #6
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    You are missing a comma between field names in each of the sql statements.

    In the first two there should be a comma after tblMain.StatusComment.
    In the 3rd sql statement add a comma after tblFiscalBudget.Budget

  7. #7

    Thread Starter
    Junior Member
    Join Date
    May 2009
    Posts
    28

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

    I have added them to your post above.

    Please clarify that a bit..

    "an error" is virtually useless because there are millions of errors, and we don't know which one you are getting unless you tell us.

    We also don't know what code (if any) you were using to get the error, or where that code was.

    My Apologies about the code. I am a very new beginner with access and programming.
    Please see my first post that explains the error I run into.
    When I click debug, it shows my code but highlights, Me.RecordSource = sql
    How do I fix this?

  8. #8
    Fanatic Member Psyrus's Avatar
    Join Date
    Jul 2000
    Location
    Gallifrey
    Posts
    602

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    See brucevde's post. You are missing commas in 3 spots in your sql string.

    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment," _
    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment, " _
    & "tblFiscalBudget.FiscalYear AS FiscalYear, tblFiscalBudget.Budget, " _
    Chris

    VB 6.0 Calendar App Video Gamers Group
    Don't forget to rate people if they helped you.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    May 2009
    Posts
    28

    Re: Run Time Error'3075': Syntax Error(Missing Operator) in query expression

    Quote Originally Posted by Psyrus View Post
    See brucevde's post. You are missing commas in 3 spots in your sql string.

    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment," _
    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment, " _
    & "tblFiscalBudget.FiscalYear AS FiscalYear, tblFiscalBudget.Budget, " _
    Thank you very much brucevde for helping me find the problem! And psyrus for reminding me of it! Your help is very much appreciated. Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.