SQL Runtime Error 3075

[silentwolf]

Hi guys,

Not sure if the information I am giving is enough for you to help me.

This is a code I try to run but I am getting an runtime Error 3075 missing element or in query it is in the WHERE Clause..

Private Sub btnTest_Click()
    Dim strSQL As String
    Dim strSQLTeil As String

    Dim Dateiname As String


    Select Case Me!fraStatus
        Case 1
            strSQLTeil = "tblStatus.staID=1"
        Case 2
            strSQLTeil = "tblStatus.staID=2"
        Case Else
            strSQLTeil = "tblStatus.staID>0"
     End Select
     
strSQL = "SELECT tblObjekte.objID, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAdresse, tblObjekte.objOrt, tblStatus.staID " & _
"FROM tblStatus INNER JOIN (tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objKunIDRef) ON tblStatus.staID = tblObjekte.objStatIDRef " & _
"WHERE [COLOR=#ff0000](((tblObjekte.objID)=[Formulare]![frmObjektAuswahl]![cboTest]) AND " & strSQLTeil[/COLOR]
    
    CurrentDb.QueryDefs("qryObjektTest").SQL = strSQL
    
         DoCmd.OpenQuery "qryObjektTest"
End Sub

does anyone of you guys know where I this is wrong?

Thanks

 

[chicagocomputerclasses]

At least one of these "(" is extra "WHERE (((

 

[Norie]

Does this work?

Private Sub btnTest_Click()
Dim strSQL As String
Dim strSQLTeil As String
Dim Dateiname As String

    Select Case Me!fraStatus
        Case 1
            strSQLTeil = "tblStatus.staID=1"
        Case 2
            strSQLTeil = "tblStatus.staID=2"
        Case Else
            strSQLTeil = "tblStatus.staID>0"
    End Select

    strSQL = strSQL & " SELECT tblObjekte.objID, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAdresse, tblObjekte.objOrt, tblStatus.staID "
    strSQL = strSQL & " FROM tblStatus INNER JOIN (tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objKunIDRef) ON tblStatus.staID = tblObjekte.objStatIDRef "
    strSQL = strSQL & " WHERE tblObjekte.objID=[Formulare]![frmObjektAuswahl]![cboTest] AND " & strSQLTeil

    CurrentDb.QueryDefs("qryObjektTest").Sql = strSQL

    DoCmd.OpenQuery "qryObjektTest"
    
End Sub

 

[silentwolf]

Hi Norie,
thanks for your help!
Still not filtering it right but no more error so I guess I need to look into the query a bit closer there is somewhere a problem..

Will have a look!

Thanks!

 

[silentwolf]

Hi chicagocomputerclasses,

thanks will look at your suggestion too !

 

[Norie]

Have you tried testing the query by outputting it to the Immediate Window and then running it manually?

 

[silentwolf]

Norie,
I never done that how do you run a query from the immediate window shame

 

[Norie]

No, you output the SQL statement created in the code to the Immediate Window with something like this.

Debug.Print strSQL

Then you copy the statement, create a new query, paste the statement in the SQL view of the query and then hit Run.

 

[james_lankford]

take this
and make a new query out of it

but change all the references to forms to actual values

SELECT 
  tblObjekte.objID, 
  IIf 
  (
    [kunFirma] Is Null, 
    [kunVorname] & "" "" & [kunNachname], 
    [kunFirma] 
  ) AS Kontakt, 
  tblObjekte.objAdresse, 
  tblObjekte.objOrt, 
  tblStatus.staID  
FROM 
  tblStatus 
  INNER JOIN 
  (
    tblKunden 
    INNER JOIN 
      tblObjekte 
      ON 
        tblKunden.kunID = tblObjekte.objKunIDRef
  ) 
  ON 
    tblStatus.staID = tblObjekte.objStatIDRef 
WHERE 
(
  (
    tblObjekte.objID = [Formulare]![frmObjektAuswahl]![cboTest] 
  )
  AND 
  (
    tblStatus.staID = 1
  )
)

so change this
tblObjekte.objID = [Formulare]![frmObjektAuswahl]![cboTest]
to
tblObjekte.objID = 29
or whatever it should be

see if you get any results back

 

[silentwolf]

Well of course I used debug.print but never used it with SQL and paste it back into the sql query.. hmm something new every day lol
I will try!

Thanks for the moment I let you know how I am going with the code!!