?
Solved

WHERE clause needs brackets

Posted on 2009-02-12
8
Medium Priority
?
222 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:davecocks
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:Shawn Cøady
ID: 23624452
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

0
 
LVL 28

Expert Comment

by:TextReport
ID: 23624463
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

0
 
LVL 1

Author Comment

by:davecocks
ID: 23624497
Hi Shawn_SanDiego:,

Thanks for your help.

I'm still getting a 3075 error though
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 28

Expert Comment

by:TextReport
ID: 23624525
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
0
 
LVL 1

Author Comment

by:davecocks
ID: 23624615
Hey Andrew,

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

0
 
LVL 28

Accepted Solution

by:
TextReport earned 2000 total points
ID: 23624684
Remove the qry.traitID AND after teh WHERE.
If this still fails the stop the code with a BreakPoint (F9) or a STOP command after the line of code sets the variable and do a Debug.Print appendtraits
Then copy the text into the posting here. Pleas remember vbCrLf at the end of the lines I put in, it makes the SQL more readable
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.charID Not IN (Select t.traitID FROM tblTraitData t)" & vbcrlf

Open in new window

0
 
LVL 1

Author Comment

by:davecocks
ID: 23624915
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

0
 
LVL 1

Author Closing Comment

by:davecocks
ID: 31546187
Cheers matey!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question