Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL with 2 criteria giving me a headache 1

Status
Not open for further replies.

EddyLLC

Technical User
Mar 15, 2005
304
US
Here's an easy one I'm sure. I'm going cross-eyed looking at it. I use the following SQL to pull a record using 2 criteria. The recordset opens fine using everything except the "Answers.Race = 'White' portion. With the entire statement I get the Syntex Error (Missing Operator) error the portion of the statement starting with "Having Answers.aTopic" is included in the message.
Thanks for the fresh look.

strSQL = "SELECT Answers.aTopic, Answers.Race, Sum(" & strTest & ")/ " & intNoWhite & " AS SumOfAnswer2 " _
& "From Answers GROUP BY Answers.aTopic, Answers.Race " _
& "Having Answers.aTopic = " & cboSubject1 & " AND Answers.Race = 'White'
 
Out of curiosity, when you did this

Debug.Print strSql

Didn't the problem stand out? When I did it it was obvious that there is an extra space here in the division
Sum(strTesT)/ 1 AS SumOfAnswer2

and there is no closing ' here
Answers.Race = 'White
 
White space doesn't matter in SQL.
I do see a closing single-quote around 'White'

What I don't see is single-quotes around the TOPIC filter.

Code:
strSQL = "SELECT Answers.aTopic, Answers.Race, Sum(" & strTest & ")/ " & intNoWhite & " AS SumOfAnswer2 " _
        & "From Answers GROUP BY Answers.aTopic, Answers.Race " _
        & "Having Answers.aTopic = [!]'[/!]" & cboSubject1 & "[!]'[/!] AND Answers.Race = 'White'"

In fact, if I had to guess, I would say that cboSubject1 probably represents a string with multiple words (at least one space). If it was a single word, you would probably have gotten a different message.

For example, suppose cboSubject1 was the string "Hello". The query would look like this.

Having Answers.aTopic = Hello And Answers.Race = 'White'

Without the single-quotes, SQL would interpret this to be a column in the Answers table instead of data.

if cboSubject1 was a string like "Hello World", the sql would be.

Having Answers.aTopic = Hello World And Answers.Race = 'White'

Since the hello doesn't have single quotes, SQL inerprets this to ba a column name, and then expects and AND or an OR to follow.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
You are correct George, cboSubject1 is a multiple word string, specifically a name, for example "Dr. John Smith". I made the changes as shown below, both eliminated the space and surrounded the cboSubject1 with single quotes and came up with the following error; Syntax Error(missing operator) in query expression 'Answer.Race WHERE ANSWER.aTopic = 'Dr. John Smith' AND Answers.Race = 'White".


"SELECT Answers.aTopic, Answers.Race, Sum(" & strTest & ")/" & intNoWhite & " AS SumOfAnswer2 " _
& "From Answers GROUP BY Answers.aTopic, Answers.Race " _
& "WHERE Answers.aTopic = '" & cboSubject1 & "' AND Answers.Race = 'White'
 
Please ignore the last post I made. Trying different wording I replaced Having with Where. Using "Having" with the changes suggested the SQL worked fine.

Thanks for the responses and especially for the explanation. Details like those added really aid in my learning.

Eddy
 
Trying different wording I replaced Having with Where

I almost recommended this in my first reply. I write a lot o SQL queries. Whenever I write a query that involves an aggregate (like sum, min, max, avg, etc...) I usually use a where clause to filter simple data, and the having clause to filter aggregate data. The trick is, your where clause must be before the GROUP by clause, and a HAVING clause comes after the group by.

Ex:

Code:
Select Id, Sum(Column) As Total
From   TableName
Where  OtherColumn = 'Blah'
Group By ID
Where  Sum(Column) > 100


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 

Also, sooner or later you wil find out that sometimes in:
Code:
WHERE ANSWER.aTopic = '[blue]Dr. John Smith[/blue]' AND Answers.Race = 'White'
You will find name like:
Code:
WHERE ANSWER.aTopic = '[red]Dr. Susie O'Brian[/red]' AND Answers.Race = 'White"
So I would suggest:
Code:
Answers.aTopic = " & [blue]Replace(cboSubject1, "'", "''")[/blue] & " AND

Have fun.

---- Andy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top