Get data for only last three months

[poppy]

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

 

[Tom Ogilvy]

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

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

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, "_", " "))