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?

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.

Quote:

---

Originally Posted by 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.

---

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?

Can you post the SQL string so I can take a peek at it?

Quote:

---

Originally Posted by Perito

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

Quote:

---

Originally Posted by Perito

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?

Quote:

---

Originally Posted by mcleyn

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!