Page 3 of 3 FirstFirst 123
Results 31 to 40 of 40
  1. #31
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes!! Here is the syntax making that magic happen!!

    Code:
    Option Explicit
    
    Sub AutoOpen()
    'Series of If Statements must be updated after the holiday in a given year passes
      Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
      Dim oCC As ContentControl
      If Date = #1/2/2014# Then
        iDelay = 2     ' post New Years Day
      ElseIf Date = #1/21/2014# Then
        iDelay = 4     ' post Martin Luther King's Day
      ElseIf Date = #2/18/2014# Then
        iDelay = 4     ' post President's Day
      ElseIf Date = #5/27/2014# Then
        iDelay = 4     ' post Memorial Day
      ElseIf Date = #7/7/2014# Then
        iDelay = 4     ' post Independence Day
      ElseIf Date = #9/3/2013# Then
        iDelay = 4     ' post Labor Day
      ElseIf Date = #12/2/2013# Then
        iDelay = 5     ' post Thanksgiving Day Weekend
      ElseIf Date = #12/26/2013# Then
        iDelay = 2     ' post Christmas Day
      ElseIf Format(Now(), "ddd") = "Mon" Then
        iDelay = 3
      Else
        iDelay = 1
      End If
      sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
      
      Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
      oCC.Range.Text = sDate
    End Sub

    And here is some code that would be dynamic. I have commented in Red to point out my questions\concerns.
    Please let me know what you think?

    Code:
    Option Explicit
     
    Sub AutoOpen()
    Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
    Dim oCC As ContentControl
    If Date = DateSerial(Year(Date), 1, 2) Then    
        iDelay = 2 ' post New Years Day
    ElseIf Date = NDow(Year(Date), 1, 3, vbMonday) Then   (I can see that vb[day of week] tells the 1, 3 to pick the 3rd week in the month of January since it is 1, 3 or 1(January), 3(3rd week of January)  
                                                                                       But it would have to be that line + 1 right? Because we don't work on that day so we would work on Tuesday, the day after the 3rd Monday in January (not sure the correct syntax for + 1 day???    
    iDelay = 4 ' post Martin Luther King's Day
    ElseIf Date = NDow(Year(Date), 2, 3, vbMonday) Then    (same + 1 syntax needed here)
        iDelay = 4 ' post President's Day
    ElseIf Date = NDow(Year(Date), 5, 4, vbMonday) Then     (same + 1 syntax needed here)
        iDelay = 4 ' post Memorial Day
    ElseIf Date = DateSerial(Year(Date) ,4,7) Then       (Not sure if this one is correct--I think it should be (Year(Date), 7, 5) Then      
    (and what would happen if July 5th was a weekend?? Maybe after the line Can we put something like '& If Date = DateSerial(Year(Date), 7, 5, vbMonday) Then iDelay = 3 but If Date = DateSerial(Year(Date), 7, 5, vbTuesday) Then iDelay = 4 but If Date = DateSerial(Year(Date), 7, 5, vbWednesday) Then iDelay = 2, etc., etc., etc....... 
    
    or maybe I have a function or you have a function that accounts for whether or not the holiday is a weekend or not???? 
    
    I think instead of repeating for every day of weekday, maybe we can make it say something like 'ElseIfDate = DateSerial(Year(Date), 7, 5) & WeekDay(wDate) = 1, Go to WeekDay(wDate) = 1 + 1, Then iDelay = 3 (since July 4th would fall on a Sunday and would be treated just like a normal Monday) Or if it were WeekDay(wDate) = 7, Go to WeekDay(wDate) = 7 + 2, Then iDelay = 3 (since July 4th would fall on a Saturday and need 2 days added to be treated like a normal Monday)
    
    This is a function that might help:  
    ' Days of the Week: 1 = Sunday, 2 = Monday, 3 = Tuesday, etc., etc., etc.......
    Public Function IsWeekend(wDate As Date) As Integer
            If WeekDay(wDate) = 7 Or WeekDay(wDate) = 1 Then Is Weekend = 1
    End Function
    
    
        iDelay = 4 ' post Independence Day
    ElseIf Date = NDow(Year(Date), 9, 1, vbMonday) Then   (Would this work for let's say September of 2015 where the first Monday of September is on the 7th which is actually the second week of September???)
        iDelay = 4 ' post Labor Day
    ElseIf Date = NDow(Year(Date), 11, 4, vbThursday) Then   (this would need a + 4 since we would come back to work the following Monday right??)    
    iDelay = 5 ' post Thanksgiving Day Weekend
    ElseIf Date = DateSerial(Year(Date),12,26) Then
        iDelay = 2 ' post Christmas Day
    ElseIf WeekDay(Date) = vbMonday Then
        iDelay = 3
    Else
        iDelay = 1
    End If
    sDate = Format(Date - iDelay, "dddd, MMMM dd, yyyy")
     
    Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
    oCC.Range.Text = sDate
    End Sub
     
    ' We can generalize this to holidays that are defined as the Nth Day of some month,
    ' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
    ' The following function will return the Nth DayOfWeek for a given month and year:
    'SYntax NDOW:
    '   y = Year
    '   M = Month
    '   N = Nth day of M month
    '   DOW = Day of the week:  1 = Sunday, 2= Monday, etc.
    Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
        NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
    End Function
    Last edited by LukeB; 2013-07-11 at 13:48.

  2. #32
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Sigh - if it was easy I would have done it for you. The first set of code needs to be much more complicated to ensure you haven't fallen back onto a weekend or other holiday. In Australia if a holiday such as Christmas falls on a Sunday then the public holiday is held on the 26th of December. If that happens for you then the code needs to deal with that too.

    I'm not even going to start with the second code.

    I still think this problem should be solved outside the box rather than inside the box.

    Alternative Option #1 - The people who print the original output turn on the Printer Overlay function using the printer driver so the date gets added to the original output AS it is printed so when it arrives on your desk it is already date stamped with THE DAY it was printed. If your printer doesn't support overlay files then the files could be output to Acrobat and a date overlay could be added there before output.

    Alternative Option #2 - Document has code which tracks the day the document was last opened (which is always a work day since you won't open it on the weekend or on a holiday). Then when you open the document the code reads the previous opening date and inserts that onto the page. This method is actually dead simple to code and is far more fault tolerant than your best holiday manipulations.
    Code:
    Sub AutoOpen()
      Dim dOpenedLast As Date, oCC As ContentControl
      'read the previous date opened from the stored property
      dOpenedLast = CDate(ActiveDocument.BuiltInDocumentProperties("Subject"))
      'If the file wasn't opened earlier today then change the date shown to that date
      If dOpenedLast <> Date Then
        Set oCC = ActiveDocument.SelectContentControlsByTitle("Mail Room Receive Date").Item(1)
        oCC.Range.Text = dOpenedLast
      End If
      'reset the last opened date to today
      ActiveDocument.BuiltInDocumentProperties("Subject") = Format(Date, "d MMMM yyyy")
    End Sub
    Sub AutoClose()
      ActiveDocument.Save
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #33
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yawn-Let me show you some VBA code to solve this schedule set up inside the box (getting confused with you "Alternative Options"??)

    #1: The people who print the original output are hospitals and doctor's offices throughout the region (not at our company address) who mail them through the mail....These are physical papers when I get them (no software files involved here) which I remove all staples, place in printer tray, run my macro document by opening it and print 1,000 copies, for example (as long as I put a number higher than how many I put in the tray so that when they are finished, I can cancel the job with all pages having the date stamp).

    #2: Like I said in #1, no software files involved here (wish they were, hopefully someday not too far when they make an e-portal to handle all claims, but not sure if that will happen 100% since many doctors prefer to mail in paper claims, other reasons I am not aware of right now, etc., etc.)

    Code:
    Option Explicit
    Private Type typHoliday
        bIsHoliday As Boolean
        strDay As String
    End Type
    Sub AutoOpen()
        StampPreviousBusinessDay
    End Sub
    Sub StampPreviousBusinessDay()
        Dim oDate As Date
        Dim oDateStamp As Date
        Dim typDay As typHoliday
         'oDate = "12/2/2013" 'Test for Thanksgiving Day
         'oDate = "12/27/2011" 'Test for Chistmas Day Observed
         'oDate = "1/3/2011" 'Test for New Years Day Observed on Friday
         'oDate = "1/3/2017" 'Test for New Years Day Observed on Monday.
         'oDate = "5/27/14" 'Test for Memorial Day
        oDate = "5/27/14" 'Test for Independence Day Observed on Friday.
         'Unset and use this when done testing.
         oDate = Now
        oDateStamp = DateAdd("d", -1, oDate)
        Do
            typDay = fcnIsHolidayOrWeekend(oDateStamp)
            If typDay.bIsHoliday Then
                oDateStamp = DateAdd("d", -1, oDateStamp)
            End If
        Loop While typDay.bIsHoliday
        ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1).Range.Text = Format(oDateStamp, "dddd, MMMM dd, yyyy")
    lbl_Exit:
        Exit Sub
    End Sub
    Public Function fcnIsHolidayOrWeekend(oDate As Date) As typHoliday
        Select Case True
        Case Weekday(oDate) = 1 Or Weekday(oDate) = 7
             'Plain weekend day.
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Weekend day"
        Case oDate = DateSerial(Year(oDate), 1, 1)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "New Year's Day"
        Case oDate = DateSerial(Year(oDate), 12, 25)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Christmas Day"
        Case oDate = DateSerial(Year(oDate), 7, 4)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Independence Day"
        Case oDate = DateSerial(Year(oDate), 11, 11)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Veterans Day"
        Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 11, 4, 5)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Thanksgiving Day"
        Case oDate = DateAdd("d", 1, fcnNumbered_DayOfWeek(Year(oDate), 11, 4, 5))
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Thanksgiving Recovery Day"
        Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 1, 3, 2)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Martin Luther King's Birthday"
        Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 2, 3, 2)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Presidents Day"
        Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 9, 1, 2)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Labor Day"
        Case oDate = fcnNumbered_DayOfWeek(Year(oDate), 5, fncNumberOfNamedDaysInMonth(Year(oDate), 5, 2), 2)
            fcnIsHolidayOrWeekend.bIsHoliday = True
            fcnIsHolidayOrWeekend.strDay = "Memorial Day"
        Case Else
             'Is it an observed holiday
            If Weekday(oDate) = 2 Then
                Select Case True
                Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 1, 1)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "New Year's Day Observed"
                Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 12, 25)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Christmas Day Observed"
                Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 7, 4)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Indenpence Day Observed"
                Case DateAdd("d", -1, oDate) = DateSerial(Year(oDate), 11, 11)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Veteren's Day Observed"
                Case Else
                    fcnIsHolidayOrWeekend.bIsHoliday = False
                    fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
                End Select
            ElseIf Weekday(oDate) = 6 Then
                Select Case True
                Case oDate = "12/31/" & Year(oDate)
                    If DateAdd("d", 1, oDate) = DateSerial(Year(oDate) + 1, 1, 1) Then
                        fcnIsHolidayOrWeekend.bIsHoliday = True
                        fcnIsHolidayOrWeekend.strDay = "New Year's Day Observed"
                    End If
                Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 12, 25)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Christmas Day Observed"
                Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 7, 4)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Indenpence Day Observed"
                Case DateAdd("d", 1, oDate) = DateSerial(Year(oDate), 11, 11)
                    fcnIsHolidayOrWeekend.bIsHoliday = True
                    fcnIsHolidayOrWeekend.strDay = "Veteren's Day Observed"
                Case Else
                    fcnIsHolidayOrWeekend.bIsHoliday = False
                    fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
                End Select
            Else
                fcnIsHolidayOrWeekend.bIsHoliday = False
                fcnIsHolidayOrWeekend.strDay = "Just and ordinary day"
            End If
        End Select
    lbl_Exit:
        Exit Function
    End Function
    Public Function fcnNumbered_DayOfWeek(lngYear As Long, lngMonth As Long, lngNumber As Long, lngDayOfWeek As Long) As Date
        fcnNumbered_DayOfWeek = DateSerial(lngYear, lngMonth, (8 - Weekday(DateSerial(lngYear, lngMonth, 1), (lngDayOfWeek + 1) Mod 8)) + ((lngNumber - 1) * 7))
    lbl_Exit:
        Exit Function
    End Function
    Public Function fncNumberOfNamedDaysInMonth(lngYear As Long, lngMonth As Long, lngDayOfWeek As Long) As Long
        Dim lngIndex As Long, lngRange As Long
        On Error GoTo lbl_Err
        lngRange = Day(DateSerial(lngYear, lngMonth + 1, 0))
        fncNumberOfNamedDaysInMonth = 0
        For lngIndex = 1 To lngRange
            If Weekday(DateSerial(lngYear, lngMonth, lngIndex)) = lngDayOfWeek Then
                fncNumberOfNamedDaysInMonth = fncNumberOfNamedDaysInMonth + 1
            End If
        Next lngIndex
    lbl_Exit:
        Exit Function
    lbl_Err:
        fncNumberOfNamedDaysInMonth = 0
        Resume lbl_Exit
    End Function

  4. #34
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew (or recent poster), did you send a reply or modify one of the messages because I got an email 7/21/13 at 2:50AM from this forum, but when I looked, my latest post still shows as the last one posted and I don't see any post that looks different\new\"catches my eye"??

  5. #35
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Luke

    I didn't post a reply but there was recently some spamming messages posted on this board and the moderators such as myself try to get onto these quickly and delete the posts before many people see them.

    I haven't replied because I don't have the time to attempt to resolve the code you have posted. I still believe the Alternative #2 code I posted earlier will do what you require without the need to plot the working days either side of a public holiday for the next millennium. I wish you the best of luck on your quest though - it is an interesting project but not one I want to solve for free. Perhaps someone else will step in to help you out.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #36
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's cool....

    Alternative #2: Once again Andrew, these are physical paper claims that I can touch and feel (no software files even though I wish they could go electronic and make my life that much easier with a fully automated business process). But anyway, the code I posted does do all of that, it doesn't matter what year, millennium, etc.

    I can't be the only chimp who has ventured out to solve something like this........

  7. #37
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Alternative 2 doesn't need the hard copies to be in electronic form. It is the code I posted to use in a docm version of the file you already had. It works by always knowing the previous work day because that was the day the document was last opened.

    The code basically doesn't get called on weekends or holidays (because you aren't working then) so when you open the template it recalls the date it was last opened and displays that (while storing today's date for the next day you open the doc). This would work every working day of the year - always pointing back to the previous work day.

    It's true that you can't be the only person who has tried to solve this question but most others would consider the flaws in your labelling logic. If the paper arrives on your desk on a Monday, how can you be POSITIVE that it was actually received by your 'mail box' last Friday and not dropped in by hand sometime over the weekend or even earlier today. Giving it a stamp that could conceivably pre-date when it really arrived is IMO dangerous. Doctors work 24 hours a day and on public holidays too don't they? Backdating to another date is an educated guess and hackable by anyone who knows your system of document handling.

    Most people would simply stamp the received paperwork on the date that you start processing it (ie today). Whilst this may not be the ACTUAL date that the mail arrived it is perfectly accurate, straightforward and how most people would do it. If you changed the label to 'Processing commenced: ddmmyy' then there would be no ambiguity in the labelling and your accuracy problem would be solved. If you have key performance indicators that dictate you must label the paper with the date it was received by post then it really should be processed on that day (or held in a locked repository until it is).
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #38
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I see what you're saying there and that is another way of doing it and makes sense.

    As far as the business process rules, it is what it is (that's what my manager needs it to be). When my company receives it in the mail, that is the date that it needs the date stamp. It is held in a locked repository. I totally agree with you on the timing of when the mail could possibly be dropped off. For all we know, I could put whatever date I wanted on there and say "yeah, it was received that day". I believe a more perfect system would be something like bar scanning all mail items when they are received (but even then if someone drops it into a drop box or drops it off on Saturday when mail room employees are enjoying there weekend off or if, like you say, they receive it earlier in the day, let's say someone drops it off at 2am in the morning for whatever reason, then the date stamp could possibly be inaccurate). It is a flawed system, but nothing is perfect and as long as the Claims Processors process the claims early enough to avoid just meeting the deadlines, there should be no penalty assessed. Doctors could work 24 hours a day and on public holidays and when we get the claim in the mail, we will date stamp it according to when we receive those claims for procedures given to patients on holidays, weekends, 24 hours a day, whatever (you get the point, right?).... and yes, those claims would be subjected to an imperfect system\business process that we have in place as of now just like all other claims, whether the procedure(s) given took place on regular business days, weekends, holidays, whatever (you know what I am saying?).....

    Anyway, I am not sure if I have that code you are referring to.....can you post it up again or tell me what number post it is so I can check?

  9. #39
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    The code was in post #32

    That code combined with the sample doc we got to earlier is attached.
    Attached Files Attached Files
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #40
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew for all your help with this (it is really great),

    Can you please check out this post: "IIf statement for Member Eligibility" under the Access forum. I can't seem to come up with any solution for the problem I am asking about in that, my newest post....

Page 3 of 3 FirstFirst 123

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •