"Monthiversary"

[dgtampa]

Look back at post 3. I did suggest this would be an issue.

Hello June7; I noticed that when I reviewed the thread, but I just assumed RagJose's function accounted for that. But we know what happens when we assume...

Need to compare number of days for current month with day of admit date. If admit date day is greater than current month days and the review date is in the last week then need to return True. Modify the function to include this logic.

I would love it if I were able to do this on my own! However, I would not have been able to even get where I am without your -and RagJose's- help. I am going to take a whack at it anyway though, because I love to learn about it and I am learning quite a bit from everyone!

Just curious though; in this particular case, why does the existing function work for "Monthiversaries" like the 31st in other instances where I have used the function, but not in this particular one? I have lots of patients/beneficiaries with "Monthiversaries" which don't occur in every month, but the function has been working for them...

Thank you for your help!

[June7]

Use debugging techniques to try and track down the reason. Refer to the link at bottom of my post.

A quick test of the function shows that a monthiversary of 29, 30, 31 will be captured by review dates of 8/2/2012 and 8/30/2012. Is twice in one month a valid output?

Whereas, no Thursday review date in April 2012 or June 2012 or July 2012 will capture a monthiversary of the 31st. However, 4/4/2013 will capture the 31st. Look at a calendar as you follow the function's logic and the reason might become obvious. You will see that 2/28/2013 will capture the 31st but no Thursday review date in March 2013 will.

I am not sure there is an easy solution for what you want. Contributing reasons are 1) dates shift through the week from year to year and 2) first and last weeks of months not always 7 days and don't always include a Thursday.

I recommend that the first of each month you just do a listing of the clients ordered by the day of the AdmitDate and start from the top.

[dgtampa]

Hello again June7,

I have spent a couple of days dealing with a pesky tropical storm. Actually, we are still dealing with it here in Florida and are wondering if it will ever leave...

In any case; I appreciate your feedback. Some comments below.

A quick test of the function shows that a monthiversary of 29, 30, 31 will be captured by review dates of 8/2/2012 and 8/30/2012. Is twice in one month a valid output?

This is true that they will technically be reviewed twice in one month, but that is a valid output for us; as long as they are captured.

For our purposes dates such as the 29th, 30th, and 31st default to the last day of smaller months (i.e. a monthiversary of the 31st would be lumped in with monthiversaries for the 30th of April, the 28th of February, etc.)

Whereas, no Thursday review date in April 2012 or June 2012 or July 2012 will capture a monthiversary of the 31st.

The 31st of March (Sammy's monthiversary) captured by the review date 3/29/2012, while Sammy's review for April was captured by 5/3/2012. The same is true for July; as you pointed out above Sammy's review for July and August would be captured within the same month, but it was captured nonetheless.

The month of June, however (the month of your mysterious moniker) is an anomaly for the DoReview function because the monthiversary technically falls "in-between" two review ranges, depending on which Thursday review date is picked (i.e. June 28th or July 5th).

I am going to crunch on this some more; I'll let you know what I come up with, if anything.

Thanks again for your input; it has helped me visualize the problem more clearly...

[dgtampa]

Ok,

I thought I was going in the right direction (I was able to get the function to return the illusive "not-oft-recurring" monthiversary dates), but now it won't return the rest of the dates. I don't think I have the blocks in the right order or something. (Note: June7, I have been reading the informational site you posted about debugging and I'm trying to understand it; however, my head is already throbbing from trying to create code as a novice. In summary: I haven't been able to debug my code...)

Here's what I have so far (the parts I added are in orange):

Function DoReview(ThursDate As Date, Admit_Date As Date) As Boolean
Dim SunBef As Date, SatAf As Date, MonAf As Date
Dim AdmitDay As Integer, Sun_Day As Integer, Sat_Day As Integer

SunBef = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) - 4)  'Date of the preceding Sunday
SatAf = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) + 2)   'Date of the subsequent Saturday
MonAf = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) + 3) 'Date of subsequent Monday for days 29 thru 31
Sun_Day = Day(SunBef)   'Day of the month
Sat_Day = Day(SatAf)
Monday_After = Day(MonAf)
AdmitDay = Day(Admit_Date)

DoReview = False

If Sun_Day < Sat_Day And Sat_Day > Monday_After Then   'Tests for months which end on Saturday and which dont contain days 29 thru 31
    If AdmitDay > Sat_Day And (AdmitDay = 29 Or AdmitDay = 30 Or AdmitDay = 31) Then
        DoReview = True
    End If
  ElseIf Sun_Day < Sat_Day Then   'week contained in a single month
    If AdmitDay >= Sun_Day And AdmitDay <= Sat_Day Then
        DoReview = True
    End If
  Else                    'week ending on next month
    If AdmitDay >= Sun_Day Or AdmitDay >= Sat_Day Then
        DoReview = True
    End If
End If
End Function

What am I doing wrong? I don't know if I am understanding how ElseIf conditions and Else conditions run if the previous condition is not met...

[ssanfu]

I might be way out in left field since I'm not sure I understand what the problem is, but you could try the following code.
Dates are stored as a number - a double - where the part to the left of the decimal represents the number of days since a beginning date (I forgot what the date is ... Dec31,1899 ??? ) and the part to the right of the decimal is the time. If you save the date to a long integer, you will have the date without the time component. Then do the date math using the Long integer. Won't have to worry about if the two Days are in the same Month.......

Comment out your current code (to save it) and paste in the following:

Function DoReview(ThursDate As Date, AdmitDate As Date) As Boolean
   Dim AdmitDay As Long, Sun_Day As Long, Sat_Day As Long
   
   '   If Weekday(ThursDate) <> vbThursday Then
   '      MsgBox ThursDate & " is not a Thursday"
   '      Exit Function
   '   End If

    ' use the DateAdd() function instead of the DateSerial() function.
   Sun_Day = DateAdd("d", -4, ThursDate)   'Date of the preceding Sunday
   Sat_Day = DateAdd("d", 2, ThursDate)   'Date of the subsequent Saturday

   AdmitDay = AdmitDate

   DoReview = False

   '???? not sure why this is here. The code is the same for the TRUE part and the FALSE part.
   If Sun_Day < Sat_Day Then   'week contained in a single month
      If AdmitDay >= Sun_Day And AdmitDay <= Sat_Day Then
         DoReview = True
      End If
   Else                      'week ending on next month
      If AdmitDay >= Sun_Day Or AdmitDay <= Sat_Day Then
         DoReview = True
      End If
   End If
End Function

My $0.02 .........


Edit: just saw the "OR" in the FALSE part of the IF(). Have to study more and read better.

[dgtampa]

Thank you Steve, for your help. However, I could not get the function to work (not sure why) even after making some corrections to the variable names. Even then, I'm not sure it would have solved my problem. That being said...

I figured out the problem and fixed the DoReview function! I feel like I know something about VBA now! (Wait...that could be dangerous...)

All I did was modify the code as follows (changes are in ORANGE):

Function DoReview(ThursDate As Date, Admit_Date As Date) As Boolean

Dim SunBef As Date, SatAf As Date, SunAf As Date
Dim AdmitDay As Integer, Sun_Day As Integer, Sat_Day As Integer, Sunday_After As Integer

SunBef = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) - 4)  'Date of the preceding Sunday
SatAf = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) + 2)   'Date of the subsequent Saturday
SunAf = DateSerial(Year(ThursDate), Month(ThursDate), Day(ThursDate) + 3) 'Date of subsequent Sunday for days 29 thru 31
Sun_Day = Day(SunBef)   'Day of the month
Sat_Day = Day(SatAf)
Sunday_After = Day(SunAf)
AdmitDay = Day(Admit_Date)

DoReview = False
If Sun_Day < Sat_Day And Sat_Day < Sunday_After Then 'week contained in a single month
    If AdmitDay >= Sun_Day And AdmitDay <= Sat_Day Then
        DoReview = True
    End If
  ElseIf Sun_Day < Sat_Day And Sat_Day > Sunday_After Then   'Tests for months which end on Saturday and which dont contain days 29 thru 31
    If AdmitDay > Sat_Day And (AdmitDay = 29 Or AdmitDay = 30 Or AdmitDay = 31) Or AdmitDay >= Sun_Day And AdmitDay <= Sat_Day Then
        DoReview = True
    End If
  Else                    'week ending on next month
    If AdmitDay >= Sun_Day Or AdmitDay <= Sat_Day Then
        DoReview = True
    End If
End If
End Function

I also fixed an issue with the name of a couple of variables/identifiers because it was wrongly named and potentially confusing; the function correctly evaluated months ending on a Saturday, but the variable names MonAf and Monday_After were changed to SunAf and Sunday_After, respectively.

Thank you all for your help. I'll mark this thread Solved! Oh, wait...it already was... ;-)