"Monthiversary"

[dgtampa]

I have a database set up to store information about "beneficiaries," or residents/patients, in an in-patient recovery program where I work. We would like to use this database to query which beneficiaries will be reviewd at our staff meetings each week.

We are required to review each of the 140-plus residents on a monthly basis. Program staff members hold these review meetings every Thursday. They use the "day" of a given beneficiary's admit date/start date (i.e. the 22nd of each month for a beneficiary who was admitted on 1/22/2012) to select which beneficiaries are reviewed on a given Thursday in this manner:

-weeks at the center begin on Sunday and end on Saturday;


-If a beneficiary's "monthiversary" day falls within a given Sunday and Saturday, they are reviewed the Thursday within that week;
-(example: in deciding who we will review on Thursday, April 19th, 2012, we will select those beneficiaries which have a "monthiversary" day which falls between Sunday (4/15/2012) and Saturday (4/21/2012) of that week (to include the 15th and the 21st).

The table which stores beneficiary information contains the following fields:
Last_Name, First_Name, Admit_Date, Counselor, Room_Number, WTA, ISEstat

The field "Admit_Date" contains the data which we use for review scheduling.

I don't know enough about SQL to write such a complicated parameter query; I have also considered/am open to the idea that I may need to modify or add to or modify my db in order to make this work. Is anyone able to help? I have already searched around quite a bit for a solution, or to try and piece one together, but to no avail!

[RagJose]

Seems to me the solution might require a small VBA Function that you could use in your query; weeks crossing month boundaries pose conditions too complicated indeed to handle in SQL. Will give it a thought and come back with a suggestion.

[June7]

How do you want to handle admit dates that are not in every month, i.e. February has only 28 days except for leapyear, 4 other months have only 30 days. So patient with admit date of 1/30/2011 has a 'monthiversary' day not in February.

Consider the following which takes the AdmitDate day and compares to last day of current month. If it is >= use the last day, if < use the AdmitDate day:

x = field name for Admit Date

Create field in query with expression:
AdmitDay: IIf(Day([x])>=IIf(Month(Date())=12,31,Day(CDate((Month(Date() )+1 & "/1/" & Year(Date())))-1)),IIf(Month(Date())=12,31,Day(CDate((Month(Date( ))+1 & "/1/" & Year(Date())))-1)),Day([x]))

Criteria for that field:
Between [Enter from 1 to 31 as Sunday Date] And [Enter from 1 to 31 as Sunday Date]+6

As you can see, the expression gets complicated very quickly. This suggestion works only for planning within the current month. If you want to plan farther ahead, gets more complicated and RJ's suggestion for a VBA function is probably best approach.

[RagJose]

Hi, there follows a VBA function that, given a review date and an admit date, returns True or False for including the beneficiary in the review. It circumvents the issue raised by June7 concerning months with less than 31 days. It employs the intrinsic DateSerial function, which gracefully traverses month boundaries. You'll need to insert the DoReview function in a Module. Your query to list the beneficiaries to be reviewed at a given date could be like

"SELECT {applicable fields} WHERE DoReview ([Enter review date],[Admit_Date])".

As it is, when you open the query it will prompt for the review date. You can substitute a form field instead. Hope this helps!

Function DoReview(ThursDate As Date, AdmitDate As Date) As Boolean
Dim SunBef, SatAf As Date
Dim AdmitDay, Sun_Day, 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
Sun_Day = Day(SunBef)   'Day of the month
Sat_Day = Day(SatAf)
AdmitDay = Day(AdmitDate)

DoReview = False

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

[ssanfu]

FYI,
You have defined SunBef, AdmitDay & Sun_Day as variants. In Access, you must explicitly declare variable types.
So you would need to use

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

[June7]

As ssanfu points out, the declaration must be done for each variable, there is no 'mass' declaration for the entire line. If not explicitely declared, the variable will be treated as a variant. The code will still work but variants are supposed to occupy more memory, which may or may not be an issue.

[RagJose]

Well, life is indeed a continuous learning experience. This is something very "basic" which I never realized in about 20 years of VBA. Guess I may have presumed a Fortran behavior, where a type declaration affected all variables in the statement. Thanks, Steve and J7! YYHH (Yes You Have Helped).

dgtampa, would appreciate your feedback on whether this thread has helped solving your question.

[ssanfu]

In many languages , such as C, C++, Visual BASIC & FORTRAN, you can declare several variables in one statement without having to repeat the data type.
In VBA, each variable must have a declared data type. Strange isn't it?? VBA is a "subset" of VB, but there are still many differences.

[dgtampa]

Well, life is indeed a continuous learning experience. This is something very "basic" which I never realized in about 20 years of VBA. Guess I may have presumed a Fortran behavior, where a type declaration affected all variables in the statement. Thanks, Steve and J7! YYHH (Yes You Have Helped).

dgtampa, would appreciate your feedback on whether this thread has helped solving your question.

Hello! I just returned from my weekend and saw that there was some real discussion regarding my post! Thank you for all of your help and thoughts (RagJose, June7, and ssanfu)!

I have not been able to read through the posts; I need to process this information and try out the suggestions so that I may give you all some feedback. You should be hearing from me real soon... Thank you again!

[dgtampa]

Ok,

First, thank you for your help; the code looks great (from the LITTLE I know about VBA) and I think I understand how it works...

I have put the code you provided into a VBA module, with the changes/additions that ssanfu suggested. Now the code looks like this:

Function DoReview(ThursDate As Date, AdmitDate As Date) As Boolean
Dim SunBef As Date, SatAf 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
Sun_Day = Day(SunBef)   'Day of the month
Sat_Day = Day(SatAf)
AdmitDay = Day(AdmitDate)
DoReview = False
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

I then added the expression to a simple query of "tblBeneficiaries" like so:

"SELECT {Last_Name, First_Name} WHERE DoReview ([Enter review date],[Admit_Date])"

When I run the query it does not prompt me for the review date parameter; instead the query returns this:
Query Returns.zip

I am a novice at this, so I'm probably placing the expression in the wrong place, or something just as obvious. Here is where I put the expression (in query design mode):
Query Design.zip

Please let me know what I can do to fix this. Thanks again!

[dgtampa]

Oh, I forgot to say that I didn't put Admit_Date in with the other two fields {Last_Name, First_Name} in place of {applicable fields} (i.e. Expr1: "SELECT {Admit_Date, Last_Name, First_Name} WHERE DoReview ([Enter review date],[Admit_Date])" until the second time I tried to run the query, but it still returned the same thing...

...don't know if it makes any difference

[June7]

I don't think RagJose is suggesting that you put that SELECT statement into a query - it IS the query. Actually, it is incomplete because the source table is not identified. Do not use {} symbols.

Open the query designer to the SQL View window and copy/paste or type statement:

SELECT Last_Name, First_Name FROM tblBeneficiaries WHERE DoReview([Enter review date],[Admit_Date])=True;

[dgtampa]

Thanks for the clarification June7...

Wow! This works MARVELOUSLY! Thank you all so much for your help! I hope this also helps someone else!

[dgtampa]

Hello everyone,

I was originally on this thread and I am posting regarding the same query and user-defined VBA function for "Monthiversary" (which RagJose so graciously provided to me 2 months ago) so I don't know if it is ok for me to post here or whether I should start a new thread...

I've not had an issue with this function and query since I started using it...until now. I have a beneficiary/patient (lets call him "Sammy")whose admit date is on the 31st; his "Monthiversary" would be on the 31st of each month, or if the month in question does not contain 31 days, it would be based on the last day of the month. Fine.

Today I ran the query for review date Thursday, 6/28/2012 (so the function returns "Monthiversaries" between Sunday, June 24th and Saturday, June 30th), but the query did not return Sammy in the results. I then tried a query for 7/5/2012 (which is the following week -7/1 thru 7/7), but Sammy was not included there either.

Just for curiosity's sake, I set the date parameter in the query for a "non-Thursday" date Friday, 6/29/2012 (which would be 6/25 thru 7/1) and Sammy came up in the results - likely because the query results traversed the month boundary between June and July. I thought DateSerial was able to calculate these dates...Maybe "Review_Date" needs to be defined as DateSerial as well?

This is important because I can't have reviews slip through the cracks, so your feedback would be useful. Again, I'm sorry if I should be posting a new thread, but I figure that anyone else running into this problem should be able to read this follow-up...

[June7]

Look back at post 3. I did suggest this would be an issue. 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.