Get data for only last three months
 

Hi Tom

I just checked the data that is printed out and it turns out to be th
data I need, it is just that the heading is January to March instead o
July to September. This is what I did with my code:


Code
-------------------

j = 0
For m = curMonth - 3 To curMonth - 1
j = j + 1

MerchantMonthly = "select branch, sum(qty), sum(b.vat)" & _
" from service_providers a left outer join cb b on a.sp_name = b.sp_name and month" & _
" (inv_date) = " & m & " and year(inv_date) = " & curYear & " order by a.sp_name"

If rs.State = -1 Then rs.Close

rs.Open MerchantMonthly, cn, adOpenKeyset, adLockOptimistic, adCmdText


'dump data into excel by calling function
Call PopulatePage(j)

.....

End sub
----------------------------------------------------------------------------------------------

Public Function PopulatePage(Optional y As Integer = 1)
Dim RowCount, HeaderRow, curcolumn, m

HeaderRow = 2
RowCount = HeaderRow
While rs.EOF = False
RowCount = RowCount + 1

For m = 0 To rs.Fields.Count - 1
curcolumn = IIf(m = 0, 1, ((y - 1) * (rs.Fields.Count - 1)) + m + 1)

If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value = MonthName(y) _

msSheet.Cells(HeaderRow - 1, curcolumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow - 1, curcolumn).Font.Size = 12
msSheet.Cells(HeaderRow - 1, curcolumn).Font.Bold = True
msSheet.Cells(HeaderRow - 1, curcolumn).Interior.Color = vbBlue

msSheet.Cells(HeaderRow, curcolumn).Value = ProperCase(Replace(rs.Fields(m).Name, "_", " "))
msSheet.Cells(HeaderRow, curcolumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow, curcolumn).Font.Size = 12
msSheet.Cells(HeaderRow, curcolumn).Font.Bold = True
msSheet.Cells(HeaderRow, curcolumn).Interior.Color = vbBlue
msSheet.Cells(RowCount, curcolumn).Value = rs.Fields(m).Value
Next m
rs.MoveNext
Wend

End Functio
-------------------



What do I do now

--
popp
-----------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...fo&userid=1145
View this thread: http://www.excelforum.com/showthread.php?threadid=26604

Get data for only last three months
 

If the current code does everything you want except it puts the wrong month
name in the first row, the problem is he

If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value = MonthName(y)

I suggest you pass both m and j to your procedure

Public Function PopulatePage(x as Integer, Optional y As Integer = 1)

.. . .
If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value = MonthName(x)
.. . .
End Function

so you would call it with

Call PopulatePage(m, j)

--
Regards,
Tom Ogilvy



"poppy" wrote in message
...

Hi Tom

I just checked the data that is printed out and it turns out to be the
data I need, it is just that the heading is January to March instead of
July to September. This is what I did with my code:


Code:
--------------------

j = 0
For m = curMonth - 3 To curMonth - 1
j = j + 1

MerchantMonthly = "select branch, sum(qty), sum(b.vat)" & _
" from service_providers a left outer join cb b on a.sp_name = b.sp_name
and month" & _
" (inv_date) = " & m & " and year(inv_date) = " & curYear & " order by
a.sp_name"

If rs.State = -1 Then rs.Close

rs.Open MerchantMonthly, cn, adOpenKeyset, adLockOptimistic, adCmdText


'dump data into excel by calling function
Call PopulatePage(j)

.....

End sub
------------------------------------------------------------------------
----------------------

Public Function PopulatePage(Optional y As Integer = 1)
Dim RowCount, HeaderRow, curcolumn, m

HeaderRow = 2
RowCount = HeaderRow
While rs.EOF = False
RowCount = RowCount + 1

For m = 0 To rs.Fields.Count - 1
curcolumn = IIf(m = 0, 1, ((y - 1) * (rs.Fields.Count - 1)) + m + 1)

If m = 1 Then msSheet.Cells(HeaderRow - 1, curcolumn).Value =
MonthName(y) _

msSheet.Cells(HeaderRow - 1, curcolumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow - 1, curcolumn).Font.Size = 12
msSheet.Cells(HeaderRow - 1, curcolumn).Font.Bold = True
msSheet.Cells(HeaderRow - 1, curcolumn).Interior.Color = vbBlue

msSheet.Cells(HeaderRow, curcolumn).Value =
ProperCase(Replace(rs.Fields(m).Name, "_", " "))
msSheet.Cells(HeaderRow, curcolumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow, curcolumn).Font.Size = 12
msSheet.Cells(HeaderRow, curcolumn).Font.Bold = True
msSheet.Cells(HeaderRow, curcolumn).Interior.Color = vbBlue
msSheet.Cells(RowCount, curcolumn).Value = rs.Fields(m).Value
Next m
rs.MoveNext
Wend

End Function
--------------------



What do I do now?


--
poppy
------------------------------------------------------------------------
poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=266048