Newbie to VBA:how to define a very long string?

[jeff06]

Following string has compilation error. what is the problem?
Is there a better way to define very long sql string?
Thanks

sqlstring = " select sum(FundingBalanceAtDefaultedDate) as totalDefaultedD, " & _
" sum(fundedamount) as TotalFundedD," & _
" sum(FundingBalanceAtDefaultedDate)/ sum(fundedamount) as RateD," & _
" sum(isdefaulted) as totalDefauledN, " & _
" count(*) as totalfundedD, " & _
" 1.0*sum(isdefaulted)/count(*) as rateN" & _
" from " & _
" ( " & _
" Select *, " & _
" case when FundingBalanceAtDefaultedDate>0 then 1 else 0 end as isdefaulted" & _
" from rpt_contractdetails" & _
" where firstfundingdate>'1/1/2004' and firstfundingdate<'1/1/2007' "& _
" and fundedamount>0 & _
" ) as a"

[gnod]

try this.. just continue the rest of your sql script


sqlstring = " select sum(FundingBalanceAtDefaultedDate) as totalDefaultedD, "
sqlstring = sqlstring & " sum(fundedamount) as TotalFundedD,"
sqlstring = sqlstring & " sum(FundingBalanceAtDefaultedDate)/ sum(fundedamount) as RateD,"

[jeff06]

Thanks.

[johnske]

what you have is fine except for the missing quotation mark at the end of the second last line i.e. should be

sqlstring = " select sum(FundingBalanceAtDefaultedDate) as totalDefaultedD, " & _
" sum(fundedamount) as TotalFundedD," & _
" sum(FundingBalanceAtDefaultedDate)/ sum(fundedamount) as RateD," & _
" sum(isdefaulted) as totalDefauledN, " & _
" count(*) as totalfundedD, " & _
" 1.0*sum(isdefaulted)/count(*) as rateN" & _
" from " & _
" ( " & _
" Select *, " & _
" case when FundingBalanceAtDefaultedDate>0 then 1 else 0 end as isdefaulted" & _
" from rpt_contractdetails" & _
" where firstfundingdate>'1/1/2004' and firstfundingdate<'1/1/2007' " & _
" and fundedamount>0" & _
" ) as a"