Hello all,
I know I am doing something wrong in my Do While loop in the steps. While this code is functional, I would like to make it more elegant. I feel like I am lobotomizing my date value in Step 2, any ideas? The format of my Date field in Access is yyyymm and it is stored as a string.
Code:
Public Function ImportMonthlyCallSummary()
Dim MaxDateInMonthly_Reports_Queue_Call_Summary As Date
Dim TodaysDate As Date
'This is how I get last month's date every time.
YesterdaysDate = DatePart("M", (Date)) & "/1/" & DatePart("yyyy", (Date))
YesterdaysDate = DateAdd("D", -1, (YesterdaysDate))
'========
'This is how this script gets the last date value in the table.
MaxDateInMonthly_Reports_Queue_Call_Summary = CDate(Format(DLast("Date", "Monthly_Reports_Queue_Call_Summary"), "####/##"))
'========
' This whole Do Loop is how the data is loaded.
' The MaxDateInMontyhly_Reports* is the last value in the table. YeserdaysDate is last months last date.
Do While MaxDateInMonthly_Reports_Queue_Call_Summary < YesterdaysDate
' This adds one month to the MaxDateInMontyhly_Reports* variable.
MsgBox ("Step 1")
MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
' This adds two months to the max date value. So, if the MaxValue Variable is 20160901 then it is 20161101.
MaxDateInMonthly_Reports_Queue_Call_Summary = DateAdd("M", 2, MaxDateInMonthly_Reports_Queue_Call_Summary)
MsgBox ("Step 2")
MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
' This lowers the playing field for the end of month date values. Some are 31, 30, or 29 etc. This ensures all dates are the 1st for every month.
MaxDateInMonthly_Reports_Queue_Call_Summary = DatePart("M", (MaxDateInMonthly_Reports_Queue_Call_Summary)) & "/1/" & DatePart("yyyy", (MaxDateInMonthly_Reports_Queue_Call_Summary))
MsgBox ("Step 3")
MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
' This subtracts one day from the "leveled" date value from the above step.
MaxDateInMonthly_Reports_Queue_Call_Summary = DateAdd("D", -1, (MaxDateInMonthly_Reports_Queue_Call_Summary))
MsgBox ("Step 4")
MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Monthly_Reports_QUEUE_CALL_SUMMARY_Import_Specific", TableName:="Monthly_Reports_Queue_Call_Summary", FileName:="\\NetworkLocation\QUEUE_CALL_SUMMARY_" & Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd") & ".csv", hasfieldnames:=True
Loop
For Each t In CurrentDb.TableDefs
If t.Name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
Next
End Function