SQL with filter not working

[EddyLLC]

I'm trying to get the totals for Answer1 for each record with the size field being large. Using the line below I get the error "Expected End of Statement" and the word Large highlighted. I'm sure it's a simple change but I can't seem to figure it out. Thanks for any help.

strSQL = "SELECT Answers.Size,Sum(Answer1)/7 AS TotAns1," _
& "FROM Answers Group by Answers.Size HAVING (Answers.Size = "Large")

 

[tedsmith]

Shouldn't have a comma before FROM, just a space.
Use a single ' anywhere inside the statement instead of "

strSQL = "SELECT Answers.Size,Sum(Answer1)/7 AS TotAns1 " _
& "FROM Answers Group by Answers.Size HAVING (Answers.Size = 'Large')"

I find it is quicker to try out SQL statements in MSAccess first bearing in mind the few conversion differences.
You can cut and paste the SQL statement directly.

 

[EddyLLC]

Thanks for the help and the suggestion. Using Access will decrease my learning curve.

 

[tedsmith]

Beware when using variables for comparisons.
Example :-
If the comparison is a String
MyVariableString="Large"
strSQL = "SELECT Answers.Size,Sum(Answer1)/7 AS TotAns1 " _
& "FROM Answers Group by Answers.Size HAVING Answers.Size = '" & MyVariableString & "'"

If the comparison is a number
MyVariableNumber="12345"
strSQL = "SELECT Answers.Size,Sum(Answer1)/7 AS TotAns1 " _
& "FROM Answers Group by Answers.Size HAVING Answers.Size = " & MyVariableNumber

If the comparison is a date
MyVariableDate="1/2/2011"
strSQL = "SELECT Answers.Date,Sum(Answer1)/7 AS MyDate " _
& "FROM Answers Group by Answers.Date HAVING Answers.Date = #" & MyVariableDate & "#"

 

[MajP]

faq705-6112

 

[Andrzejek]

... or my favorite;

strSQL = "SELECT Answers.Size,Sum(Answer1)/7 AS TotAns1," _
        & "FROM Answers Group by Answers.Size HAVING (Answers.Size = "Large")" 
[blue]
Debug.Print strSQL [/blue]

This way you can SEE what your computer would SEE in strSQL

Have fun.

---- Andy

 

[tedsmith]

Well my favourite is to not disturb the code by inserting debug statements that can get left there accidentally.
Instead I just put a break point after the statement then when the program halts, I type -
?strSQL in the immediate window

Some SQL code form Access may be slightly different in syntax to what is required in vb - see the help. But when you paste it across it usually tells you where the error is except for my points I mentioned above.

 

[Andrzejek]

I agree with you and try to remember to comment out the Debug statements in my code. But... aren't the Debug ignored when EXE is made by the compiler in VB?

Have fun.

---- Andy

 

[gmmastros]

Debug statements are completely ignored by the compiler.

In my own app, I usually remove them because they eventually get annoying after you fix the problem.

-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

 

[tedsmith]

The reason I don't like debug is not that it affects the complied result but when you disturb code you have already written, it is possible to accidentally create a problem in another area without realising it.
Particularly with beginners it can be hard to find a mistake you have unwittingly introduced in something you just had previously running pefectly.

(One of the rules of quality assurance)