I would much prefer to tweak the SQL rather than doing it while loopinf through the record set!! This is a stacked bar chart so the code below would have to be repeated three times...messy....
Anyhow this is what I was geting at when talking about solving it outside of the SQL. it works however far from a sooth solution:
NOTE - I have hard coded the start and finish dates for testing
dim startMNum,finishMNum,startYear,finishYear,fromVar, tovar,runningVar,numMonths
dim count
numMonths = 0
startMNum = month("01/08/2007")
finishMNum = month("31/08/2008")
startYear = year("01/08/2007")
finishYear = year("31/08/2008")
fromVar = startYear * 100 + startMnum
toVar = finishYear * 100 + finishMnum
runningVar = fromvar
do while runningVar <= toVar
numMonths = numMonths + 1
if runningVar = startYear * 100 + 12 then
runningVar = (startYear + 1) *100 +1
else
runningVar = runningVar + 1
end if
loop
dim runningMonth,runningYear
runningMonth = startMNum
runningYear = startYear
count = 0
for count = 1 to numMonths
if not values.Eof then
if values(2) = runningMonth AND values(3) = runningYear then
Chart.AddGroupedData left(uCase(values(0)),3) & " " & right(values(3),2), "All Incidents",values(1) , "FFFF00"
values.moveNext
else
Chart.AddGroupedData left(uCase(monthName(runningMonth,false)),3) & " " & right(runningYear,2), "All Incidents", 0 , "FFFF00"
end if
end if
if runningMonth = 12 then
runningMonth = 1
runningYear = runningYear + 1
else
runningMonth = runningMonth + 1
end if
next
Wind is your friend
Matt
www.elitemarquees.com.au