Link to home
Start Free TrialLog in
Avatar of davecocks
davecocks

asked on

WHERE clause needs brackets

Hi,

I'm missing some operators somewhere

Run Time error 3075, missing operators ()

somewhere in the where clause
Dim appendTraits As String
appendTraits = "INSERT INTO tblTraitData ( speciesID, charID, traitID, value1 ) " & _
"SELECT " & listSpecies.Value & " As speciesID, qry.charID, qry.traitID, 0 AS value1 " & _
"FROM tblTraitsNotInTraitData qry " & _
"WHERE (" & listSpecies.Value & " as speciesID) AND qry.traitID AND qry.charID Not IN " & _
"(Select t.speciesID, t.traitID, t.charID FROM tblTraitData t);"

Open in new window

Avatar of Shawn Cøady
Shawn Cøady
Flag of United States of America image

I believe your IN clause needs ()

Dim appendTraits As String
appendTraits = "INSERT INTO tblTraitData ( speciesID, charID, traitID, value1 ) " & _
"SELECT " & listSpecies.Value & " As speciesID, qry.charID, qry.traitID, 0 AS value1 " & _
"FROM tblTraitsNotInTraitData qry " & _
"WHERE (" & listSpecies.Value & " as speciesID) AND qry.traitID AND qry.charID Not IN (" & _
"(Select t.speciesID, t.traitID, t.charID FROM tblTraitData t));"
 

Open in new window

You don't need the  as speciesID in the where

Removed from WHERE
(" & listSpecies.Value & " as speciesID) AND

so it looks like
INSERT INTO tblTraitData ( speciesID, charID, traitID, value1 )
SELECT " & listSpecies.Value & " As speciesID, qry.charID, qry.traitID, 0 AS value1
FROM tblTraitsNotInTraitData qry
WHERE qry.traitID AND qry.charID Not IN (Select t.traitID, FROM tblTraitData t)

Cheers, Andrew
Dim appendTraits As String
appendTraits = "INSERT INTO tblTraitData ( speciesID, charID, traitID, value1 ) " & vbcrlf & _
               "SELECT " & listSpecies.Value & " As speciesID, qry.charID, qry.traitID, 0 AS value1 " & vbcrlf &_
               "FROM tblTraitsNotInTraitData qry " & vbcrlf & _
               "WHERE qry.traitID AND qry.charID Not IN (Select t.traitID FROM tblTraitData t)" & vbcrlf

Open in new window

Avatar of davecocks
davecocks

ASKER

Hi Shawn_SanDiego:,

Thanks for your help.

I'm still getting a 3075 error though
The error is coming from "WHERE (" & listSpecies.Value & " as speciesID)
You do not use AS in the WHERE also there is no comparison being done.
Cheers, Andrew
Hey Andrew,

your solution works but doesn't return any records to append. Any suggestions?

ASKER CERTIFIED SOLUTION
Avatar of TextReport
TextReport
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Cool Thanks Andrew,

and also thanks for the tips on the code!!

I'll post an extension of this question soon.

appendTraits = "INSERT INTO tblTraitData ( speciesID, charID, traitID, value1 ) " & vbCrLf & _
               "SELECT " & listSpecies.Value & " As speciesID, qry.charID, qry.traitID, 0 AS value1 " & vbCrLf & _
               "FROM tblTraitsNotInTraitData qry " & vbCrLf & _
               "WHERE qry.traitID Not IN (Select t.traitID FROM tblTraitData t)" & vbCrLf

Open in new window

Cheers matey!