okay, this has officially driven me nuts.
The current code looks to see if the folder "mmmm-yyyy" (i.e. June-2005) exist and if not creates the folder and downloads the reports (8 of them) into the folder with the date attached to the end of the file.
The problem is that now since I am going from 8 reports downloaded a day to 14, I need to further break down the folders.
I need for the code to first see if there is a folder with the correct month and then look to see if there is a folder with the correct day and if the day does not exist, create it.
Here is the current code that works. (I have already tested this and it simply keeps adding daily reports to June-2005 and this is becoming rather large and hard to look through).
Dim strwhere As String
Dim strwhere1 As String
Dim strwhere2 As String
Dim mdofreport As String
mdofreport = Format([EndDate], "mm-dd-yyyy")
Dim myDir1
Dim dirDate1 As String
dirDate1 = Format([EndDate], "mmmm-yyyy")
myDir1 = Dir("I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1, vbDirectory)
If myDir1 = "" Then
MkDir ("I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1)
End If
strwhere1 = "([CC Summary 2nd Level] = 1340) OR " _
& "([CC Summary 2nd Level] > 3999 AND " _
& "[CC Summary 2nd Level] <> 9500 AND " _
& "[CC Summary 2nd Level] Not Between 7700 And 7799)"
strwhere2 = "([Labor Code] In ('D', 'P', 'K', 'H', 'J', 'E', 'N'))"
strwhere = "(" & strwhere1 & ")" & " AND " & "(" & strwhere2 & ")"
gstrTitle = "Direct Manpower"
DoCmd.OpenReport "E - Offline Plant Summary Avg", acViewPreview, , strwhere
DoCmd.OutputTo acOutputReport, "E - Offline Plant Summary Avg", acFormatSNP, "I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1 & "\Weekly Summary Offline Plant" & " " & gstrTitle & " " & mdofreport & ".snp"
DoCmd.CLOSE acReport, "E - Offline Plant Summary Avg"
The current code looks to see if the folder "mmmm-yyyy" (i.e. June-2005) exist and if not creates the folder and downloads the reports (8 of them) into the folder with the date attached to the end of the file.
The problem is that now since I am going from 8 reports downloaded a day to 14, I need to further break down the folders.
I need for the code to first see if there is a folder with the correct month and then look to see if there is a folder with the correct day and if the day does not exist, create it.
Here is the current code that works. (I have already tested this and it simply keeps adding daily reports to June-2005 and this is becoming rather large and hard to look through).
Dim strwhere As String
Dim strwhere1 As String
Dim strwhere2 As String
Dim mdofreport As String
mdofreport = Format([EndDate], "mm-dd-yyyy")
Dim myDir1
Dim dirDate1 As String
dirDate1 = Format([EndDate], "mmmm-yyyy")
myDir1 = Dir("I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1, vbDirectory)
If myDir1 = "" Then
MkDir ("I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1)
End If
strwhere1 = "([CC Summary 2nd Level] = 1340) OR " _
& "([CC Summary 2nd Level] > 3999 AND " _
& "[CC Summary 2nd Level] <> 9500 AND " _
& "[CC Summary 2nd Level] Not Between 7700 And 7799)"
strwhere2 = "([Labor Code] In ('D', 'P', 'K', 'H', 'J', 'E', 'N'))"
strwhere = "(" & strwhere1 & ")" & " AND " & "(" & strwhere2 & ")"
gstrTitle = "Direct Manpower"
DoCmd.OpenReport "E - Offline Plant Summary Avg", acViewPreview, , strwhere
DoCmd.OutputTo acOutputReport, "E - Offline Plant Summary Avg", acFormatSNP, "I:\Paul\Weekly Attendance Reports\Smyrna-Decherd\" & dirDate1 & "\Weekly Summary Offline Plant" & " " & gstrTitle & " " & mdofreport & ".snp"
DoCmd.CLOSE acReport, "E - Offline Plant Summary Avg"
