Trouble with code that is broken over multiple lines (space_)
- From: mr tom <mr-tom at mr-tom.co.uk.(donotspam)>
- Date: Mon, 7 Jan 2008 07:23:01 -0800
I've got the following code (generated by recording a macro), but it doesn't
work. I've not modified it. I'm sure it's because of the way it's spanning
lines.
Apologies - I'm not sure whether it's possible to duplicate the formatting
here, so I'll paste all the code and then the excerpt that excel highlights
red (syntax error)
Any ideas gratefully received.
Tom.
WHOLE CODE:
Sub Macro1()
Sheets("Initial Query").Select
Range("D3").Select
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=CSCSQL02;UID=kcarswell;APP=Microsoft
Office XP;WSID=HP1847;DATABASE=dwAWD;Trusted_Connection=Yes"
.CommandText = Array( _
"SELECT DISTINCT dim_Adviser.AdviserAlias,
Sum(fct_CommissionAccrued.AccruedAmount) AS 'Sum of
AccruedAmount'"&chr(13)&""&chr(10)&"FROM dwAWD.dbo.dim_Adviser dim_Adviser,
dwAWD.dbo.dim_CommissionType dim_CommissionType, d" _
, _
"wAWD.dbo.dim_PolicyKPI dim_PolicyKPI,
dwAWD.dbo.fct_CommissionAccrued fct_CommissionAccrued,
dwAWD.dbo.vdw_OnRiskCalendar vdw_OnRiskCalendar"&chr(13)&""&chr(10)&"WHERE
fct_CommissionAccrued.AdviserID = dim_Adviser.Advise" _
, _
"rID AND fct_CommissionAccrued.CommissionTypeID =
dim_CommissionType.CommissionTypeID AND fct_CommissionAccrued.PolicyKPIID =
dim_PolicyKPI.PolicyKPIId AND vdw_OnRiskCalendar.OnRiskDateID = fct_Commiss" _
, _
"ionAccrued.PolicyOnRiskDateID AND
((dim_CommissionType.CommissionReallocatedAccType='Adviser') AND
(dim_CommissionType.CommissionSubPhase In ('Fee','Initial')) AND
(dim_PolicyKPI.KPI_OnRisk='OnRisk') " _
, _
"AND (vdw_OnRiskCalendar.TimeDate Between {ts '2007-01-01 00:00:00'}
And {ts '2007-12-31 00:00:00'}))"&chr(13)&""&chr(10)&"GROUP BY
dim_Adviser.AdviserAlias, dim_Adviser.AdviserUnused,
dim_Adviser.Department"&chr(13)&""&chr(10)&"HAVING (dim_" _
,)
.Refresh BackgroundQuery:=False
End With
End Sub
BROKEN SECTION:
..CommandText = Array( _
"SELECT DISTINCT dim_Adviser.AdviserAlias,
Sum(fct_CommissionAccrued.AccruedAmount) AS 'Sum of
AccruedAmount'"&chr(13)&""&chr(10)&"FROM dwAWD.dbo.dim_Adviser dim_Adviser,
dwAWD.dbo.dim_CommissionType dim_CommissionType, d" _
, _
"wAWD.dbo.dim_PolicyKPI dim_PolicyKPI,
dwAWD.dbo.fct_CommissionAccrued fct_CommissionAccrued,
dwAWD.dbo.vdw_OnRiskCalendar vdw_OnRiskCalendar"&chr(13)&""&chr(10)&"WHERE
fct_CommissionAccrued.AdviserID = dim_Adviser.Advise" _
, _
"rID AND fct_CommissionAccrued.CommissionTypeID =
dim_CommissionType.CommissionTypeID AND fct_CommissionAccrued.PolicyKPIID =
dim_PolicyKPI.PolicyKPIId AND vdw_OnRiskCalendar.OnRiskDateID = fct_Commiss" _
, _
"ionAccrued.PolicyOnRiskDateID AND
((dim_CommissionType.CommissionReallocatedAccType='Adviser') AND
(dim_CommissionType.CommissionSubPhase In ('Fee','Initial')) AND
(dim_PolicyKPI.KPI_OnRisk='OnRisk') " _
, _
"AND (vdw_OnRiskCalendar.TimeDate Between {ts '2007-01-01 00:00:00'}
And {ts '2007-12-31 00:00:00'}))"&chr(13)&""&chr(10)&"GROUP BY
dim_Adviser.AdviserAlias, dim_Adviser.AdviserUnused,
dim_Adviser.Department"&chr(13)&""&chr(10)&"HAVING (dim_" _
,)
.
- Follow-Ups:
- Prev by Date: Re: Does a shape cover a cell ?
- Next by Date: Font enlarge/reduce by 1 pt?
- Previous by thread: Re: Modify the IgnoreBlank property of the Validation object
- Next by thread: RE: Trouble with code that is broken over multiple lines (space_)
- Index(es):
Relevant Pages
|
