What's wrong with this SQL statement? (error 3075)

C

cboath

Registered User.
Local time
Today, 15:49
Joined
Jan 25, 2008
Messages
28
Here's the statement

Code: 
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"

If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?

I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.

Thanks
 
Trying use a subquery that handles the grouping.

edit- I didn't notice that your sql is in VBA. I've never written a subquery in VBA. I've done it in PHP, but thats another beast. This site and this site say that Access can do it too. I learned something new!

Also, I looked into why this occurs with grouped queries. According to remarks here, the Select statement is carried out AFTER the other statements. Thus, when the GROUP BY statement fires, the required fields have not been selected yet. I'm not sure why the same is not true in a simple group by query without any joins, but it does make some sense.
 
Last edited:
Don't know what happened. Closed Access, reopened it and then it decided to work.

I guess it got scared that i went to the experts for help :)
 
Building the SQL string in a variable is a good practice. That way when you are having a problem, you can stop the code after the string is built and print it into the debug window. Then you can copy it and paste it into the SQL view of the QBE. Frequently you will get more informative error messages when running the query that way.
 
That's the thing, I built it in the QBE first to get the format for the joins then converted it into code. Apparently it was fine, some hiccup in Access that was straightened out on a restarting of the program.
 
Hi

I am also having a problem with the sql part of my code. I am not familiar with sql, so am struggling.

I have a search form but when a user uses quotes, comma's etc, this line of the code shows a 3075 error

qryDef.SQL = strSQL & " " & strWhereText & " " & strOrder



Can anyone advise how I can fix it, not sure how to use sql as a variable.

Any help would be much appreciated!
 
Hi, yes I have tried that and it just highlights the line I posted but does not specifically note the issue. this is the full code:-



Private Sub Command6_Click()
Dim strSQL As String
Dim strOrder As String
Dim strWhere As String
Dim strWhereOne As String
Dim strWhereTwo As String
Dim strWhereText As String
Dim strWhereTextTwo As String
Dim dbNm As Database
Dim qryDef As QueryDef
Dim strSQLChoice As String
Set dbNm = CurrentDb()
strOrder = "ORDER BY DetailsOfRequest.Titleofchapterjournalarticle;"
strWhere = "WHERE"
strWhereText = "WHERE"
strWhereTextTwo = " AND"
' Pick the strSQL based on if the TxtComboSearch box is null
' and then if it isn't null, then based on the selection made in
' the fraSearchOpt frame (All Words, Any Word, Exact Phrase)
If IsNull(TxtComboSearch) Then
strSQLChoice = "One"
strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN" & " FROM DetailsOfRequest"
Else
strSQLChoice = "Two"
Select Case fraSearchOpt
Case 1 'All Words
strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,AllWordsExist([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,AllWordsExist([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,AllWordsExist([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,AllWordsExist([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
Case 2 'Any Word
strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,AnyWordExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,AnyWordExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,AnyWordExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,AnyWordExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
Case 3 'Exact Phrase
strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,ExactPhraseExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,ExactPhraseExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,ExactPhraseExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,ExactPhraseExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
End Select
End If
If Not IsNull(Me.txtboxField1) Then
strWhereText = strWhereText & " (DetailsOfRequest.TitleText) like '*" & Me.txtboxField1 & "*' AND"
strWhereTextTwo = strWhereTextTwo & " (DetailsOfRequest.TitleText) like '*" & Me.txtboxField1 & "*' AND"
End If
If Not IsNull(Me.txtboxField2) Then
strWhereText = strWhereText & " (DetailsOfRequest.Titleofchapterjournalarticle) like '*" & Me.txtboxField2 & "*' AND"
strWhereTextTwo = strWhereTextTwo & " (DetailsOfRequest.Titleofchapterjournalarticle) like '*" & Me.txtboxField2 & "*' AND"
End If
'Remove last AND from the SQL statement
strWhereText = Mid(strWhereText, 1, Len(strWhereText) - 5)
strWhereTextTwo = Mid(strWhereTextTwo, 1, Len(strWhereTextTwo) - 5)
'Put Search String Together
If strSQLChoice = "One" Then
Set qryDef = dbNm.QueryDefs("sqrySearch")
qryDef.SQL = strSQL & " " & strWhereText & " " & strOrder
ElseIf strSQLChoice = "Two" Then
Set qryDef = dbNm.QueryDefs("sqrySearch")
qryDef.SQL = strSQL & " " & strWhere & strWhereOne & strWhereTextTwo & strWhereTwo & strWhereTextTwo & " " & strOrder
Else
MsgBox "There is an error"
End If
DoCmd.OpenForm "sqrysearch", acViewNormal

End Sub



This is the line it hightlights as an issue. This is where my sql knowledge is very limited and I just can't see where in the code this problem is.

qryDef.SQL = strSQL & " " & strWhereText & " " & strOrder
 
Code: 
Private Sub Command6_Click()
Dim strSQL As String
Dim strOrder As String
Dim strWhere As String
Dim strWhereOne As String
Dim strWhereTwo As String
Dim strWhereText As String
Dim strWhereTextTwo As String
Dim dbNm As Database
Dim qryDef As QueryDef
Dim strSQLChoice As String
Set dbNm = CurrentDb()
strOrder = "ORDER BY DetailsOfRequest.Titleofchapterjournalarticle;"
strWhere = "WHERE"
strWhereText = "WHERE"
strWhereTextTwo = "  AND"
' Pick the strSQL based on if the TxtComboSearch box is null
' and then if it isn't null, then based on the selection made in
' the fraSearchOpt frame (All Words, Any Word, Exact Phrase)
If IsNull(TxtComboSearch) Then
    strSQLChoice = "One"
    strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN" & " FROM DetailsOfRequest"
Else
    strSQLChoice = "Two"
    Select Case fraSearchOpt
        Case 1 'All Words
            strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,AllWordsExist([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,AllWordsExist([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
            strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,AllWordsExist([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
            strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,AllWordsExist([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
        Case 2 'Any Word
            strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,AnyWordExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,AnyWordExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
            strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,AnyWordExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
            strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,AnyWordExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
        Case 3 'Exact Phrase
            strSQL = "SELECT DetailsOfRequest.Titleofchapterjournalarticle, DetailsOfRequest.TitleText, DetailsOfRequest.ID, DetailsOfRequest.LRAccessCode, DetailsOfRequest.ISBN_ISSN, IIf(IsNull([LRAccessCode]),False,ExactPhraseExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr1, IIf(IsNull([ISBN_ISSN]),False,ExactPhraseExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])) AS Expr2" & " FROM DetailsOfRequest"
            strWhereOne = " (((IIf(IsNull([LRAccessCode]),False,ExactPhraseExists([LRAccessCode],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
            strWhereTwo = " OR (((IIf(IsNull([ISBN_ISSN]),False,ExactPhraseExists([ISBN_ISSN],[Forms]![frmFirstSearch]![txtComboSearch])))=True))"
End Select
End If
If Not IsNull(Me.txtboxField1) Then
    strWhereText = strWhereText & " (DetailsOfRequest.TitleText) like '*" & Me.txtboxField1 & "*'  AND"
    strWhereTextTwo = strWhereTextTwo & " (DetailsOfRequest.TitleText) like '*" & Me.txtboxField1 & "*'  AND"
End If
If Not IsNull(Me.txtboxField2) Then
    strWhereText = strWhereText & " (DetailsOfRequest.Titleofchapterjournalarticle) like '*" & Me.txtboxField2 & "*'  AND"
    strWhereTextTwo = strWhereTextTwo & " (DetailsOfRequest.Titleofchapterjournalarticle) like '*" & Me.txtboxField2 & "*'  AND"
End If
'Remove last AND from the SQL statement
strWhereText = Mid(strWhereText, 1, Len(strWhereText) - 5)
strWhereTextTwo = Mid(strWhereTextTwo, 1, Len(strWhereTextTwo) - 5)
'Put Search String Together
If strSQLChoice = "One" Then
    Set qryDef = dbNm.QueryDefs("sqrySearch")
    qryDef.SQL = strSQL & " " & strWhereText & " " & strOrder
ElseIf strSQLChoice = "Two" Then
    Set qryDef = dbNm.QueryDefs("sqrySearch")
    qryDef.SQL = strSQL & " " & strWhere & strWhereOne & strWhereTextTwo & strWhereTwo & strWhereTextTwo & " " & strOrder
Else
    MsgBox "There is an error"
End If
DoCmd.OpenForm "sqrysearch", acViewNormal

End Sub
 
So where exactly have you Debugged the code? I cannot see a Debug.Print or MsgBox anywhere.

The syntax error would probably be because of uneven parentheses, which I cannot debug without your DB; Only you can.
 
pr2-eugin said:
Try Debugging the Error.
Click to expand...

Once your SQL string is in the immediate window you can copy it and paste it into a new query (QBE).
Try to run the query. Access will give you an error message and usually directs you to where the error is.

In addition i want to point out that your query might run very slow due to the number of IIF's which are very slow.

HTH:D
 
You must log in or register to reply here.

Similar threads

V
DLookup on a SQL statement
Replies
6
Views
412
VSolano
V
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
1,075
CJ_London
C
medihub_guy
SQL Select Statement for Quantity On Hand
Replies
18
Views
1,449
Pat Hartman
P
Gasman
Solved Total each row in crosstab query?
Replies
13
Views
1,411
Gasman
Gasman
D
Need help with SQL in an Access query that has UNION for the non-relational queries/tables
2
Replies
26
Views
1,455
dlhappy
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom