Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Visual Basic (https://www.askmehelpdesk.com/forumdisplay.php?f=469)
-   -   Run Time Error'3075': Syntax Error(Missing Operator) in query expression (https://www.askmehelpdesk.com/showthread.php?t=352328)

  • May 11, 2009, 06:09 AM
    mcleyn
    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?
  • May 11, 2009, 07:35 AM
    Perito

    There appears to be an error in the SQL query syntax (see your variable "sql"). Since I can't see the query, I can't trouble-shoot it. Make sure you are using single quotes (aka apostrophes) in SQL strings instead of normal quotation marks (aka double quotes). That's caught me a few times.
  • May 11, 2009, 08:03 AM
    mcleyn
    Quote:

    Originally Posted by Perito View Post
    There appears to be an error in the SQL query syntax (see your variable "sql"). Since I can't see the query, I can't trouble-shoot it. Make sure you are using single quotes (aka apostrophes) in SQL strings instead of normal quotation marks (aka double quotes). That's caught me a few times.

    Thank you very much for responding. I have looked over my code and I have declared the sql variable as follows:

    Dim sql As String

    I have used single quotes where necessary however I am still getting the same error as before. Do I have any other options to fix this error?
  • May 11, 2009, 08:07 AM
    Perito

    Can you post the SQL string so I can take a peek at it?
  • May 11, 2009, 08:11 AM
    mcleyn
    Quote:

    Originally Posted by Perito View Post
    Can you post the SQL string so I can take a peek at it?

    I have pasted here the procedure that I am having the error with, you can see below the highlighted line of code that is giving me the problem. Is this what you are asking for?


    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 & " '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" & statusTemp)

    'filtering on Status
    If statusTemp = "(Status <> 'Closed' and Status <> 'Rejected' and Status <> 'Withdrawn')" Then
    Me.Filter = "CA Not Signed"
    ElseIf statusTemp = "(Status = 'Project Received' or Status = 'Project Approved' " Then
    Me.Filter = "or Status = 'CA Finalized' or Status = 'CA Signed By ED')" _
    & "CA Signed"
    ElseIf statusTemp = "Status = 'CA Signed By Proponent'" Then
    Me.Filter = "Rejected"
    ElseIf statusTemp = "Status = 'Rejected'" Then
    Me.Filter = "Withdrawn"
    ElseIf statusTemp = "Status = 'Withdrawn'"
    End If

    'filtering on FiscalYear
    If (fiscalYearTemp <> " Then
    Me.Filter = ") And (Me.Filter <> ""
    End If

    'filtering on FiscalYear
    If (fiscalYearTemp <> " and (FiscalYear = '") And (Me.Filter <> "'") Then
    Me.Filter = Me.Filter & " or Status = 'Project Received')"
    ElseIf fiscalYearTemp <> " & fiscalYearTemp & " Then
    Me.Filter = "(FiscalYear = '" _
    & "' or Status = 'Project Received')"
    End If

    'filtering on Province
    If (provinceTemp <> "
    ElseIf fiscalYearTemp <> ") And (Me.Filter <> "" Then
    Me.Filter = "ON, MB, SK, AB, BC, NT & YT" & fiscalYearTemp & " and (Province = 'ON' or Province = 'MB' or Province = 'SK' "
    End If

    'filtering on Province
    If (provinceTemp <> "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')") And (Me.Filter <> "QC, NU, NB, NS, PE & NL") Then
    If cboProvince = " and (Province = 'QC' or Province = 'NU' or Province = 'NB' " Then
    Me.Filter = Me.Filter & "or Province = 'NS' or Province = 'PE' or Province = 'NL')"
    End If
    ElseIf provinceTemp <> " _
    & " Then
    If provinceTemp = "ON, MB, SK, AB, BC, NT & YT"
    ElseIf cboProvince = "(Province = 'ON' or Province = 'MB' or Province = 'SK' " Then
    Me.Filter = Me.Filter & "or Province = 'AB' or Province = 'BC' or Province = 'NT' or Province = 'YT')" _
    & "QC, NU, NB, NS, PE & NL"
    End If
    ElseIf provinceTemp <> "(Province = 'QC' or Province = 'NU' or Province = 'NB' " Then
    If provinceTemp = "or Province = 'NS' or Province = 'PE' or Province = 'NL')" Then
    Me.Filter = "" _
    & "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, "
    ElseIf provinceTemp = "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " Then
    Me.Filter = "tblMain.ProjectTitle " _
    & "FROM tblMain "
    End If
    End If

    'MsgBox (Me.Filter)

    If Me.Filter = "ORDER BY tblMain.ProjectCode;"
    ElseIf ((fiscalYearTemp = " Then
    sql = ") Or (fiscalYearTemp = "" _
    & "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment " _
    & "tblMain.ProjectTitle " _
    & "FROM tblMain "
    ElseIf ((fiscalYearTemp = "WHERE " & Me.Filter & ") Or (fiscalYearTemp = " _
    & "ORDER BY tblMain.ProjectCode;")) Then
    sql = "SELECT tblMain.ProjectCode, tblMain.Province, tblMain.EventDate, " _
    & "tblMain.AmountApproved, tblMain.Status, tblMain.StatusComment, " _
    & "tblFiscalBudget.FiscalYear AS FiscalYear, tblFiscalBudget.Budget " _
    & "tblMain.ProjectTitle " _
    & "FROM tblMain " & Me.Filter & "LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode " _
    & "WHERE " & Me.Filter & "
    Else
    sql = " _
    & "ORDER BY tblMain.ProjectCode;"
    End If

    Me.RecordSource = sql -(highlighted code)
    Me.cboStatus = statusTemp
    Me.cboFiscalYear = fiscalYearTemp
    Me.cboProvince = provinceTemp

    End Sub
  • May 11, 2009, 10:43 AM
    mcleyn
    Quote:

    Originally Posted by Perito View Post
    Can you post the SQL string so I can take a peek at it?

    These are the sql for the two queries I have.

    tblMain query:
    SELECT tblMain.Province, tblMain.ProjectTitle, tblMain.ProjectDuration, tblMain.ProjectType
    FROM tblMain
    WHERE (((tblMain.Province) Like 'ON'));

    tblMain query1:
    SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.ProjectTitle, tblMain.OrganizationName, tblMain.ContactFirstName, tblMain.ContactLastName, tblMain.ContactTelephone, tblMain.SigningFirstName, tblMain.SigningLastName, tblMain.SigningTelephone, tblMain.Timestamp
    FROM tblMain;


    Do think the problem is related to my query sql?
  • May 11, 2009, 12:05 PM
    mcleyn
    Quote:

    Originally Posted by mcleyn View Post
    These are the sql for the two queries I have.

    tblMain query:
    SELECT tblMain.Province, tblMain.ProjectTitle, tblMain.ProjectDuration, tblMain.ProjectType
    FROM tblMain
    WHERE (((tblMain.Province) Like 'ON'));

    tblMain query1:
    SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.ProjectTitle, tblMain.OrganizationName, tblMain.ContactFirstName, tblMain.ContactLastName, tblMain.ContactTelephone, tblMain.SigningFirstName, tblMain.SigningLastName, tblMain.SigningTelephone, tblMain.Timestamp
    FROM tblMain;


    Do think the problem is related to my query sql?


    Thank you Perito for all of your input. I have found the problem, I was missing a few commas is some of my sql statements. Thanks again!

  • All times are GMT -7. The time now is 02:27 AM.