Trouble with sql syntax in vba

[tallygram]

This is my first post on Access World Forums and I sure hope you can help. I have the following select statement to create an ADO recordset. When I try to run the process I get the error Incorrect syntax near '='. I have tried a full range of single and double quotes and can't seem to get it right. Can you please put me on the right path.

Thanks

"SELECT Sum(IIf([Type]= 'RESI',[Amt],0))AS Ind " & _
"Sum(IIf([Type]= 'RESM',[Amt],0))AS Med " & _
"Sum(IIf([Type]= 'RESE',[Amt],0))AS Exp " & _
"Date " & _
"FROM transaction " & _
"GROUP BY Date " & _
"WHERE ID = '" & GlobalID & "' " & _
"ORDER BY PE_Date DESC"

 

[JANR]

"SELECT Sum(IIf([Type]= 'RESI',[Amt],0))AS Ind, " & _
"Sum(IIf([Type]= 'RESM',[Amt],0))AS Med, " & _
"Sum(IIf([Type]= 'RESE',[Amt],0))AS Exp, " & _
"Date " & _
"FROM transaction " & _
"GROUP BY Date " & _
"WHERE ID = '" & GlobalID & "' " & _
"ORDER BY PE_Date DESC"

Looks like you are missing a comma to seperate the fields.

SELECT field1, field2, Field3 FROM ..... is the SQL syntax

JR

 

[vbaInet]

Welcome to the forum

Try this:

"SELECT Sum(IIf([Type]= 'RESI', [Amt], 0)) AS Ind, " & _
"Sum(IIf([Type]= 'RESM', [Amt], 0)) AS Med, " & _
"Sum(IIf([Type]= 'RESE', [Amt], 0)) AS Exp, " & _
"[Date] " & _
"FROM transaction " & _
"GROUP BY [Date] " & _
"WHERE ID = '" & GlobalID & "' " & _
"ORDER BY PE_Date DESC;"

 

[vbaInet]

Plus a two others - a space before AS and enclosing the Date field in square brackets.

 

[tallygram]

I've tried all that you suggested but am still getting the same error. Any other ideas? Is there another way to rewrite this that Access might like better?

 

[vbaInet]

Create the same query in a query designer and copy from that.

 

[Kryst51]

Try putting your space before the start of the next line as opposed to the end of the line above. Like:

"SELECT Sum(IIf([Type]= 'RESI', [Amt], 0)) AS Ind," & _
[COLOR=red]" S[/COLOR]um(IIf([Type]= 'RESM', [Amt], 0)) AS Med," & _
[COLOR=red]" S[/COLOR]um(IIf([Type]= 'RESE', [Amt], 0)) AS Exp," & _
[COLOR=red]" [[/COLOR]Date]" & _
[COLOR=red]" F[/COLOR]ROM transaction" & _
[COLOR=red]" G[/COLOR]ROUP BY [Date]" & _
[COLOR=red]" W[/COLOR]HERE ID = '" & GlobalID & "'" & _
[COLOR=red]" O[/COLOR]RDER BY PE_Date DESC;"

I think I had a similar issue once and someone told me that the space is not read if it is at the end of a quote like that, and that I needed to do it at the start of the next line, so I have gotten into the habit of doing that.

 

[boblarson]

...and someone told me that the space is not read if it is at the end of a quote like that,

Don't know what they were smoking but it isn't true. It does work at the end just fine, but it is easier to SEE at the beginning.

 

[Kryst51]

Don't know what they were smoking but it isn't true. It does work at the end just fine, but it is easier to SEE at the beginning.

Well, that's good to know. Thanks!

 

[ChipperT]

Create the same query in a query designer and copy from that.

That is a great tip. I always test my sql in query designer before attempting to use them in VBA. If it works in query designer then there is no reason for it NOT to work in VBA unless you make a mistake in breaking the lines.

 

[ChrisO]

"WHERE ID = '" & GlobalID & "' " & _

I suspect that ID is numerical so remove the single quotes around GlobalID.

Chris

 

[tallygram]

I did correct the syntax on the HAVING. I also did the debug.print for the sql query then copied and pasted that result into the sql design window of a new query in another database that has the table that I need linked. It worked just fine. I am at a loss here but sure appreciate your help.

 

[madel]

I don't see a HAVING in the code submitted above.

One tip. Remove all excess code and test with the smallest amount of code.
(Then gradually add back in a line or so at a time.)

Preferably only enough to fit on one line so you don't have too many concatenation issues.

Sub x()
Dim strSQL As String
Dim globalid As String

globalid = "fred"


strSQL = "SELECT [Amt] AS Ind FROM transaction GROUP BY [Date] WHERE ID = '" & globalid & "' " & "order by pe_date;"

Debug.Print strSQL

End Sub

 

[tallygram]

I actually figured out my problem and it had to do with the IIF statements. SQL didn't like them. When I changed them to CASE (WHEN, THEN, ELSE, END) it was much happier and so was I. It worked great. So for example, the line of code should read:
Ind = Sum(CASE WHEN [Type]= 'RESI' THEN [Amt] ELSE 0 END)," & _