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
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);"
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
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
ASKER
Hi Shawn_SanDiego:,
Thanks for your help.
I'm still getting a 3075 error though
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
You do not use AS in the WHERE also there is no comparison being done.
Cheers, Andrew
ASKER
Hey Andrew,
your solution works but doesn't return any records to append. Any suggestions?
your solution works but doesn't return any records to append. Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool Thanks Andrew,
and also thanks for the tips on the code!!
I'll post an extension of this question soon.
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
ASKER
Cheers matey!
Open in new window