SQL help

[Guest]

hi,

I have SQL generated at runtime. I added the WHERE clause, and this has
been giving error messages ever since. Basically the goal here is to get the
WHERE clause to calculates 3 dates and put this in a conditional IN criteria,
like ... WHERE [BookMth] IN('200610','200609','200510'). Here is the dynamic
SQL:


sql1 = "INSERT INTO tbl_PIVOT ( Variable, BookMth, Channel, VarCode,
VarDesc, VarNum, CountOfLoanAcct, SumOfAmount, SumOfAPR ) " & _
"SELECT '" & tabName & "' AS Variable, last13.BookMth,
last13.Channel, " & varName & " AS VarCode, " & tabName & ".descr AS VarDesc,
" & tabName & ".num AS VarNum, Count(last13.LoanAcct) AS CountOfLoanAcct,
Sum(last13.Amount) AS SumOfAmount, " & fr1 & _
" FROM last13 LEFT JOIN " & tabName & " ON " & varName & " = " &
tabName & ".grp " & _
" WHERE [BookMth] In
('Right(DateSerial(Year(Date()),Month(Date())-1,1),4) &
IIf(Right(Left(DateSerial(Year(Date()),Month(Date())-1,1),2),1)='/','0' &
Left(DateSerial(Year(Date()),Month(Date())-2,1),1),Left(DateSerial(Year(Date()),Month(Date())-1,1),2))','Right(DateSerial(Year(Date()),Month(Date())-2,1),4)
& IIf(Right(Left(DateSerial(Year(Date()),Month(Date())-2,1),2),1)='/','0' &
Left(DateSerial(Year(Date()),Month(Date())-2,1),1),Left(DateSerial(Year(Date()),Month(Date())-2,1),2))','Right(DateSerial(Year(Date()),Month(Date())-12,1),4)
& IIf(Right(Left(DateSerial(Year(Date()),Month(Date())-12,1),2),1)='/','0' &
Left(DateSerial(Year(Date()),Month(Date())-2,1),1),Left(DateSerial(Year(Date()),Month(Date())-12,1),2))'))) " & _
"GROUP BY last13.BookMth, last13.Channel, " & varName & ", " &
tabName & ".descr, " & tabName & ".num " & _
"ORDER BY last13.BookMth, last13.Channel, " & varName & ";"

I am getting a "Syntax error in INSERT INTO statement". I can't figure out
why.

Thanks in advance,
geebee

 

[Guest]

I think you can probably simplify the expression for the WHERE clause to:

………& ".grp " & _
"WHERE [BookMth] IN ('" & _
Format(DateAdd("m",-1,Date()),"yyyymm") & "','" & _
Format(DateAdd("m",-2,Date()),"yyyymm") & "','" & _
Format(DateAdd("m",-13,Date()),"yyyymm")& "') " & _
"GROUP BY …….

Ken Sheridan
Stafford, England