Access to Excel - Monthly Sum of Data for Given Date Range

[rocket5292]

Hi,
I wrote this block of code awhile ago and now I'm going back and redesigning the program I built and I know this piece can be written a lot better but I'm having a bad case of writers block on how to make it more efficient.
Here's a quick summary of what I'm trying to do. A user selects a date range in an excel userform and based on the dates they select, I query an Access database using ADO which then sums up the dollar amounts from two separate tables and subtracts the sum of one table's data from the other table's data. Sdate is the start date of the of the date range and Tdate is the end date of the date range. I know some of the variable are badly named and what not and I plan on cleaning all of that up as a rewrite it.
A couple of obstacles that I came across were what if the end date of a persons date range isnt the last day of the month and what if the date range spans multiple years. I'm not asking for someone to rewrite this for me but rather can I get some suggestions for which direction I should go in to make this more efficient? Also, if there are some obvious tips or shortcuts that I seem to be missing I would really appreciate the heads up. Thanks for the help.


xdate = DateAdd("d", -1, DateSerial(Year(tdate), Month(tdate) + 1, 1))

For dat = sdate To tdate
i = Year(dat)
MyWeekday = Day(dat)
ydate = DateAdd("d", -1, DateSerial(Year(dat), Month(dat) + 1, 1))

If Month(dat) <> Month(tdate) Or Year(dat) <> Year(tdate) Or tdate = xdate Then
If MyWeekday = 1 Then
l = l + 1

SQL2 = "SELECT Sum([ComTrackQueries]![TotalNFPOR]) AS Comparison "
SQL2 = SQL2 & "FROM Carriers INNER JOIN ComTrackQueries ON Carriers.CarrierID = ComTrackQueries.CarrierID "
SQL2 = SQL2 & "WHERE ComTrackQueries.StatementDate >= #" & dat & "# And ComTrackQueries.StatementDate < #" & Month(dat) + 1 & "/1/" & i & "# and Carriers.Carrier = '" & OneCell.Value & "'"
Debug.Print SQL2


SQL3 = "SELECT Sum([CashReceipts]![FixedFirstYear]+[CashReceipts]![VariableFirstYear]+[CashReceipts]![FixedFYMISC]+[CashReceipts]![VariableFYMISC]) AS total "
SQL3 = SQL3 & "FROM Carriers INNER JOIN CashReceipts ON Carriers.CarrierID = CashReceipts.CarrierID WHERE (CashReceipts.MonthRecognized >= #" & dat & "# And CashReceipts.MonthRecognized < #" & Month(dat) + 1 & "/1/" & i & "#) and (Carriers.Carrier = '" & OneCell.Value & "' or Carriers.Carrier = 'NFPsi(" & OneCell.Value & ")')"
Debug.Print SQL3


rcs.Open SQL2, cnt
ws1.Range("D" & l).CopyFromRecordset rcs
rcs.Close

With ws1.Range("A" & l)
.Value = MonthName(Month(dat))
.Font.Bold = True
End With

rst.Open SQL3, cnt
ws1.Range("C" & l).CopyFromRecordset rst
rst.Close

With ws1.Range("B" & l)
.Value = ws1.Range("C" & l).Value - ws1.Range("D" & l).Value
.HorizontalAlignment = xlLeft
End With
r = r + ws1.Range("C" & l).Value - ws1.Range("D" & l).Value

ws1.Range("C" & l & "" & l).Clear
ws1.Range("C" & l).Value = r

If l Mod 2 = 0 Then
ws1.Range("A" & l & ":B" & l).Interior.ColorIndex = 15
End If

End If


ElseIf MyWeekday = 1 And Day(tdate) < ydate Then
l = l + 1

SQL2 = "SELECT Sum([ComTrackQueries]![TotalNFPOR]) AS Comparison "
SQL2 = SQL2 & "FROM Carriers INNER JOIN ComTrackQueries ON Carriers.CarrierID = ComTrackQueries.CarrierID "
SQL2 = SQL2 & "WHERE ComTrackQueries.StatementDate >= #" & Month(dat) & "/1/" & i & "# And ComTrackQueries.StatementDate <= #" & tdate & "# and Carriers.Carrier = '" & OneCell.Value & "'"

SQL3 = "SELECT Sum([CashReceipts]![FixedFirstYear]+[CashReceipts]![VariableFirstYear]+[CashReceipts]![FixedFYMISC]+[CashReceipts]![VariableFYMISC]) AS total "
SQL3 = SQL3 & "FROM Carriers INNER JOIN CashReceipts ON Carriers.CarrierID = CashReceipts.CarrierID WHERE (CashReceipts.MonthRecognized >= #" & Month(dat) & "/1/" & Year(dat) & "# And CashReceipts.MonthRecognized <= #" & tdate & "#) and (Carriers.Carrier = '" & OneCell.Value & "' or Carriers.Carrier = 'NFPsi(" & OneCell.Value & ")')"

rcs.Open SQL2, cnt
ws1.Range("D" & l).CopyFromRecordset rcs
rcs.Close

With ws1.Range("A" & l)
.Value = MonthName(Month(dat))
.Font.Bold = True
End With

rst.Open SQL3, cnt
ws1.Range("C" & l).CopyFromRecordset rst
rst.Close

With ws1.Range("B" & l)
.Value = ws1.Range("C" & l).Value - ws1.Range("D" & l).Value
.HorizontalAlignment = xlLeft
End With

r = r + ws1.Range("C" & l).Value - ws1.Range("D" & l).Value

ws1.Range("C" & l & "" & l).Clear
ws1.Range("C" & l).Value = r

If l Mod 2 = 0 Then
ws1.Range("A" & l & ":B" & l).Interior.ColorIndex = 15
End If

ElseIf Day(tdate) = ydate Then...

 

[misscrf]

I am not sure what all of you requirements are, but could you round it? Like if the date put in by the user is not at the end of the month, could you round it to the end or beginning of that month, or ask them which to do?

Sorry, I didn't go through all the code, it is just a lot to look at and I have a PowerPoint headache. lol

Cleaning it up might help post readers to get through it more easily.

misscrf

It is never too late to become what you could have been ~ George Eliot