Ask Experts Questions for FREE Help !
Ask
    mcleyn's Avatar
    mcleyn Posts: 48, Reputation: 1
    Junior Member
     
    #1

    May 11, 2009, 06:09 AM
    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?
    Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #2

    May 11, 2009, 07:35 AM

    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.
    mcleyn's Avatar
    mcleyn Posts: 48, Reputation: 1
    Junior Member
     
    #3

    May 11, 2009, 08:03 AM
    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?
    Perito's Avatar
    Perito Posts: 3,139, Reputation: 150
    Ultra Member
     
    #4

    May 11, 2009, 08:07 AM

    Can you post the SQL string so I can take a peek at it?
    mcleyn's Avatar
    mcleyn Posts: 48, Reputation: 1
    Junior Member
     
    #5

    May 11, 2009, 08:11 AM
    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
    mcleyn's Avatar
    mcleyn Posts: 48, Reputation: 1
    Junior Member
     
    #6

    May 11, 2009, 10:43 AM
    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?
    mcleyn's Avatar
    mcleyn Posts: 48, Reputation: 1
    Junior Member
     
    #7

    May 11, 2009, 12:05 PM
    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!

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search


Check out some similar questions!

Prism3d.exe missing error [ 2 Answers ]

How do I fix prism3d.exe in order to play a game Wheels of Steel Convoy?

Csrcs.exe missing error at startup [ 17 Answers ]

Hello When I was checked the windows xp operating system for viruses through avg free edition it says the c:\windows\system32\csrcs.exe file is infected with worm/autoit.dja and the same have been removed from system.After rebooting the system csrcs.exe file missing error is coming in the...

Syntax Error [ 1 Answers ]

I'm in remote desktop and trying to print an email out of Microsoft Outlook and I keep getting Syntax error. I read in here to do regsvr32 ole32.dll and I received another message. DllRegister Server in ole.32.dll failed. Return code 0x80070005. Do you know what this is?


View more questions Search