Syntax error (missing operator) 2

[underpaidadmin]

I am new to Access, VB, and SQL so bear with me a little. When I run the below code it keeps giving me a syntax error, saying I am missing an operator between

tblNames.TIN Where Company="Somecompany"

Before that error I had an error with having too many characters at the end of SQL statement which I think I fixed, but this one popped up next. I did a Print of the string that generates the SQL statement in the Immediate Window just to make sure there wasn't something funky sitting there but other than a space I saw nothing.

 

[HarleyQuinn]

Could you post your whole SQL string and the method/code you are using to call it?

It might just be helpful for us to get a better overall picture of your problem.

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[underpaidadmin]

No problem. Didn't do it the first time because I wasn't sure if it was appropriate.

Private Sub btnSearch_Click()
Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName,tblNames.Company , tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN FROM (tblCases INNER JOIN tblNames ON tblCases.CaseNum = tblNames.CaseNum) INNER JOIN tblTracers ON tblCases.CaseNum = tblTracers.CaseNum GROUP BY tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName, tblNames.Company, tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN "
If Not IsNull(Me.txtCaseNum) Then
strWhere = "CaseNum = '" & Me.txtCaseNum & "'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "FirstName = '" & Me.txtFirstName & "'"
End If
'----------
If Not IsNull(Me.txtLastName) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "LastName = '" & Me.txtLastName & "'"
End If
'----------
If Not IsNull(Me.txtTIN) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "TIN = '" & Me.txtTIN & "'"
End If
'----------
If Not IsNull(Me.txtOldCaseNum) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "OldCaseNum = '" & Me.txtOldCaseNum & "'"
End If
'----------
If Not IsNull(Me.txtCompany) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Company = '" & Me.txtCompany & "'"
End If
'----------
If Not IsNull(Me.txtTracerNum) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "TracerNum = '" & Me.txtTracerNum & "'"
End If

strSQL = strSQL & "WHERE " & strWhere & ";"
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSQL)
End Sub

 

[HarleyQuinn]

At a quick glance I can see that you are adding your WHERE clause after your GROUP BY.

In SQL syntax your WHERE should appear before your GROUP BY clause.

e.g.

SELECT colA, colB
FROM tbl1
WHERE colA = "A"
GROUP BY colA, colB

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

And if a field (like CaseNum I guess) is numeric then this

strWhere = "CaseNum = '" & Me.txtCaseNum & "'"

should be

strWhere = "CaseNum =" & Me.txtCaseNum

And for dates
strWhere = "OpenDate=#" & Format(Me.txtCaseNum, "yyyy-mm-dd") & "#

 

[underpaidadmin]

Thanks JerryKlmns, currently I have no numeric fields and I have formated the dates accordingly. Now I have a new problem it seems. When I hit the search button I get an error saying I cant assign a value to this object, which it shows as the line "Me.sbfBrowse.Form.Filter=Mid(strWhere,7)".

Below is the code that is on the search form called frmSearchIt:

Private Sub btnSearch_Click()
Dim dbf As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strWhere = "WHERE "
strSQL = "SELECT tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName,tblNames.Company , tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN FROM (tblCases LEFT JOIN tblNames ON tblCases.CaseNum = tblNames.CaseNum) LEFT JOIN tblTracers ON tblCases.CaseNum = tblTracers.CaseNum "

If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCases.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.FirstName LIKE '*" & Me.txtFirstName & "*'"
End If
'----------
If Not IsNull(Me.txtLastName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.LastName LIKE '*" & Me.txtLastName & "*'"
End If
'----------
If Not IsNull(Me.txtTIN) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.TIN LIKE '*" & Me.txtTIN & "*'"
End If
'----------
If Not IsNull(Me.txtOldCaseNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OldCaseNum LIKE '*" & Me.txtOldCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtCompany) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.Company LIKE '*" & Me.txtCompany & "*'"
End If
'----------
If Not IsNull(Me.txtTracerNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblTracers.TracerNum LIKE '*" & Me.txtTracerNum & "*'"
End If
'----------
If IsDate(Me.txtOpenDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate >= " & "#" & Me.txtOpenDateFrom & "#"
End If
'----------
If IsDate(Me.txtOpenedDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate <= " & "#" & Me.txtOpenedDateTo & "#"
End If
'----------
If IsDate(Me.txtCloseDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate >= " & "#" & Me.txtCloseDateFrom & "#"
End If
'----------
If IsDate(Me.txtCloseDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate <= " & "#" & Me.txtCloseDateTo & "#"
End If
'----------

If Len(strWhere) < 8 Then
strWhere = ""
End If

strWhere = strWhere & " GROUP BY tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName, tblNames.Company, tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN"
strSQL = strSQL & strWhere & ";"

'2 Debug lines below, delete when working
Debug.Print strWhere
Debug.Print strSQL

If Not Me.FormFooter.Visible Then
            Me.FormFooter.Visible = True
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
        Me.sbfBrowse.Form.Filter = Mid(strWhere, 7)
        Me.sbfBrowse.Form.FilterOn = True
    
'Set dbf = CurrentDb
'Set rst = dbf.OpenRecordset(strSQL)
End Sub

Below is the SQL statement used for the form, sbfBrowse, that should show the results of the above code:

SELECT tblCasesMain.* AS [View]
FROM tblCasesMain LEFT JOIN tblNameslst ON tblCasesMain.CaseNum = tblNameslst.CaseNum
ORDER BY tblCasesMain.OpenDate, tblCasesMain.CaseNum;

 

[RonRepp]

If you are grouping a SQL Statement, you should be using "HAVING" instead of "WHERE".

Just a thought.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.