By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

End of Month challenge

P: 57
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:

Expand|Select|Wrap|Line Numbers
  1.    NextMonth = DateAdd("m", 1, Form_frmMain.txtStart_Com)
  2. EndOfMonth = NextMonth - DatePart("d", NextMonth)
  3.  
  4. strStart = Format(OfficeClosed(TheDate), "mmdd")   'OfficeClosed is another module that determines whether the date is a holiday
  5. strStart = Format(TheDate, "mmdd")
  6. strEnd = Format(Form_frmMain.txtEnd_Com, "mmdd")
  7.  
  8. For counter = strStart To strEnd
  9.  
  10. If counter > Format(EndOfMonth, "mmdd") Then
  11. counter = Format(DateAdd("d", 1, EndOfMonth), "mmdd")
  12. Else
  13. counter = Format(counter, "0000")
  14. End If
  15.  
  16. 'text file is imported here
  17.  
  18. Close #1    ' Close file.
  19. Next counter  'import next file
  20.  
  21.  
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
Jun 2 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Jun 2 '08 #2

P: 57
Sorry NeoPa. Here's how the app works:

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

Expand|Select|Wrap|Line Numbers
  1. Function Auto()
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. strStart = Format(OfficeClosed(TheDate), "mmdd")
  2. strStart = Format(TheDate, "mmdd")
  3. strEnd = Format(Form_frmMain.txtEnd_Com, "mmdd")
  4.  
  5. 'Check for end of month
  6.    Dim NextMonth, EndOfMonth
  7.    NextMonth = DateAdd("m", 1, Form_frmMain.txtStart_Com)
  8.    EndOfMonth = NextMonth - DatePart("d", NextMonth)
  9.  
  10. 'Make sure all files are included if multiple dates
  11. For counter = strStart To strEnd
  12.  
  13. If counter > Format(EndOfMonth, "mmdd") Then
  14. counter = Format(DateAdd("d", 1, EndOfMonth), "mmdd")
  15. Else
  16. counter = Format(counter, "0000")
  17. End If
The file is then imported into a table (i.e., filename0531.txt) and loops to the next counter:

Expand|Select|Wrap|Line Numbers
  1. Close #1    ' Close file.
  2. Next counter  'import next file
Until the end of the counter is reached.

OfficeClosed Module:

Expand|Select|Wrap|Line Numbers
  1. Function OfficeClosed(TheDate) As Integer
  2.    TheDate = Date
  3.    OfficeClosed = False
  4.  
  5. ' Test for Monday
  6. If Weekday(TheDate) = 2 Then
  7. OfficeClosed = True
  8. TheDate = DateAdd("d", -2, TheDate)
  9. Form_frmMain.txtStart_Com = TheDate
  10. End If
  11.  
  12. ' Test for Holiday
  13. If Not IsNull(DLookup("HoliDate", "tbl_Holidays", "[HoliDate]=#" _
  14.      & [TheDate] - 1 & "#")) Then
  15.        OfficeClosed = True
  16. TheDate = DateAdd("d", -2, TheDate)
  17.  
  18. End If
  19. If Weekday(TheDate) = 1 Then
  20. TheDate = DateAdd("d", -1, TheDate)
  21. End If
  22.  
  23. Form_frmMain.txtStart_Com = Format(TheDate, "mm/dd/yyyy")
  24.  
  25. 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 :)
Jun 2 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
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).
Jun 2 '08 #4

P: 57
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).
Jun 2 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jun 3 '08 #6

P: 57
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.
Jun 3 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jun 4 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.