End of Month challenge

[DThreadgill]

I have an application that uses a date range to pull in a file for import. The filenames are by daterange (i.e., Filename0531.txt, Filename0601.txt, etc). I have the app set to automatically open the form and enter the startdate and enddate based upon today's date. (I.e., today is 06/02, the startdate of the form would be 05/31/2008 and the enddate would be 06/02/2008). The code loops through using a counter but I'm having problems. It worked up until today (I assume since the end of the month fell on a weekend). Here's my code:

   NextMonth = DateAdd("m", 1, Form_frmMain.txtStart_Com)
EndOfMonth = NextMonth - DatePart("d", NextMonth)
 
strStart = Format(OfficeClosed(TheDate), "mmdd")   'OfficeClosed is another module that determines whether the date is a holiday
strStart = Format(TheDate, "mmdd")
strEnd = Format(Form_frmMain.txtEnd_Com, "mmdd")
 
For counter = strStart To strEnd
 
If counter > Format(EndOfMonth, "mmdd") Then
counter = Format(DateAdd("d", 1, EndOfMonth), "mmdd")
Else
counter = Format(counter, "0000")
End If
 
'text file is imported here
 
Close #1    ' Close file.
Next counter  'import next file
 
 

In this case, it's capturing 06/01/2008, but not 06/02/2008 - because 0602 is greater than 0531, it's staying at 0601. Any suggestions on what needs to be done so that I don't have to touch this thing again? I'm locked down so I can't even change the date on my calendar to test.

Thanks

[NeoPa]

Your explanation leaves it unclear how the date ranges are derived (I don't want to try to work out what your question should be by reading your code. That should be for refernce only).

If you can make the situation a bit clearer I will see if I can help.

[DThreadgill]

Sorry NeoPa. Here's how the app works:

I have the startup set to a macro called autoexec which runs a module:

Function Auto()
DoCmd.OpenForm "frmMain", acNormal

The form contains unbound controls called startdate and enddate. Once the form is opened, it calls the first function, which is to find the startdate and enddate:

strStart = Format(OfficeClosed(TheDate), "mmdd")
strStart = Format(TheDate, "mmdd")
strEnd = Format(Form_frmMain.txtEnd_Com, "mmdd")
 
'Check for end of month
   Dim NextMonth, EndOfMonth
   NextMonth = DateAdd("m", 1, Form_frmMain.txtStart_Com)
   EndOfMonth = NextMonth - DatePart("d", NextMonth)
 
'Make sure all files are included if multiple dates
For counter = strStart To strEnd
 
If counter > Format(EndOfMonth, "mmdd") Then
counter = Format(DateAdd("d", 1, EndOfMonth), "mmdd")
Else
counter = Format(counter, "0000")
End If

The file is then imported into a table (i.e., filename0531.txt) and loops to the next counter:

Close #1    ' Close file.
Next counter  'import next file

Until the end of the counter is reached.

OfficeClosed Module:

Function OfficeClosed(TheDate) As Integer
   TheDate = Date
   OfficeClosed = False
 
' Test for Monday
If Weekday(TheDate) = 2 Then
OfficeClosed = True
TheDate = DateAdd("d", -2, TheDate)
Form_frmMain.txtStart_Com = TheDate
End If
 
' Test for Holiday
If Not IsNull(DLookup("HoliDate", "tbl_Holidays", "[HoliDate]=#" _
     & [TheDate] - 1 & "#")) Then
       OfficeClosed = True
TheDate = DateAdd("d", -2, TheDate)
 
End If
If Weekday(TheDate) = 1 Then
TheDate = DateAdd("d", -1, TheDate)
End If
 
Form_frmMain.txtStart_Com = Format(TheDate, "mm/dd/yyyy")
 
End Function

When I open the app, it sees today's date, sets the enddate (strEnd) as 06/02/2008, sees today as Monday, subtracts two days and sets the startdate as 05/31/2008 (strStart). It sets the counter as strStart to strEnd and loops til it reaches the end of the counter.

Thanks - I hope that's enough detail but not too much :)

[NeoPa]

Clearly you're trying to explain, which is good, but what I need is an English explanation of what it's trying to do.

For instance :-
Is it getting today's date as the End Date and the previous working day as the Start Date possibly? Is it looking for the last working week?

How do the files fit in in relation to that? Are there multiple files and it must load one for each working date between your Start and End Dates perhaps?

When that's all clear, we need to focus on the problem, so a clear explanation of what is not working as expected should complete the job. If it's simply about getting Start Date & End Date correct then the files are an irrelevance as far as the problem is concerned (It may help to paint the picture but I need to know what is relevant).

[DThreadgill]

It's been one of those days and I apologize. You've hit the nail on the head -
It only fails when the End of the Month falls on a weekend and I am trying to load the files for Saturday, Sunday & Monday):

Today's date = End Date.
StartDate: If today is Monday, then Saturday's date = start Date.

Start Date = 05/31/2008
End Date = 06/02/2008

There are multiple files and, yes, you're correct in that it must load each one for each date between the Start and end date.

Hmm...simple English - what a concept! :) Trying to give too much information just doesn't help!!!

Thank you!!!

Clearly you're trying to explain, which is good, but what I need is an English explanation of what it's trying to do.

For instance :-
Is it getting today's date as the End Date and the previous working day as the Start Date possibly? Is it looking for the last working week?

How do the files fit in in relation to that? Are there multiple files and it must load one for each working date between your Start and End Dates perhaps?

When that's all clear, we need to focus on the problem, so a clear explanation of what is not working as expected should complete the job. If it's simply about getting Start Date & End Date correct then the files are an irrelevance as far as the problem is concerned (It may help to paint the picture but I need to know what is relevant).

[NeoPa]

So, when not on a weekend (Monday) you are looking for a single date (Start Date = End Date = Date())?

When that's all clear, we need to focus on the problem, so a clear explanation of what is not working as expected should complete the job. If it's simply about getting Start Date & End Date correct then the files are an irrelevance as far as the problem is concerned (It may help to paint the picture but I need to know what is relevant).

I still need an answer for this bit.

I know you're doing your best so see this as gentle guidance rather than impatience.

[DThreadgill]

That's correct. If not a Monday, the startdate = enddate.

Not impatient at all - just thankful I've got an expert guiding me :)

So, when not on a weekend (Monday) you are looking for a single date (Start Date = End Date = Date())?

I still need an answer for this bit.

I know you're doing your best so see this as gentle guidance rather than impatience.

[NeoPa]

OK, the first thing to do is to identify the procedure that returns your list of dates then capture the dates returned from that procedure (for the date which caused the problem). Can you identify the particular procedure and post the dates returned (presumably a Saturday to Monday list). When we have that info we can progress a little further.