Function to get Thursdays in report 1

[THWatson]

I have the following module to get the # of Thursdays in a given period of time.

Function RetThur(dteStart As Date, dteEnd As Date)
Dim i As Integer
Dim intT As Integer
Dim dteTest As Date
Dim ny As Date
Dim xm As Date

   On Error GoTo RetThur_Error

    If dteStart >= dteEnd Then
        'Problem
        RetThur = "Error"
    End If
    
    For i = 0 To (dteEnd - dteStart)
        dteTest = dteStart + i
        ny = DateSerial(Year(dteTest), 1, 1)
        xm = DateSerial(Year(dteTest), 12, 25)
        
        If Weekday(dteTest) = 5 And dteTest <> ny And dteTest <> xm Then
            intT = intT + 1
        End If
    Next
    
    RetThur = intT

   On Error GoTo 0
   Exit Function

RetThur_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RetThur of Module modGetThursdays"
    
End Function

This works fine in a query, with the following column as an expression:

RetThur(Forms!frmDateSelector!txtStartDate,Forms!frmDateSelector!txtEndDate)

But I don't want this column in the query as it becomes part of a Union query...and this column creates extra results in any year which does not have 52 Thursdays.

However, if I try to put this function in the report itself I get a #Name error.

Is there a way to get this into the report without having it in the query construction?

Thanks.

Tom

 

[dhookom]

This should work in a control source as long as the form is open (and stays open) and the name of the text box is not the name of a field.

=RetThur(Forms!frmDateSelector!txtStartDate,Forms!frmDateSelector!txtEndDate)

BTW, your function returns either a number ortext. I generally try to specify a single data type for the return. You might be better off returning null or a negative number rather than "Error".

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Thanks, Duane.

By the way, we have had our 4 1/2 year old granddaughter Kennedy here with us this week - spring break in Ontario - and she is a delight. Made me wonder how your grandson is doing.

Tom

 

[dhookom]

Jackson (5 yrs) is great. He visited last night. I need to get him programming ;-)

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Well, they are quite wonderful at that age!

Tom

 

[THWatson]

Duane
What you suggested works as a text box control in the form where the user selects dates, "frmDateSelector, to find the # of Thursdays.

I also want to calculate the actual weeks in which there are Thursdays (excluding Christmas and New Years when those days fall on a Thursday) within the fiscal year of the period entered.

This works in a query. Therefore, should not the following also work in the form?

=IIf(Weekday(DateSerial(Year([Forms!formDateSelector!txtStartDate]),12,25))=5 And Weekday(DateSerial(Year([Enter End Date]),1,1))=5,50,IIf(Weekday(DateSerial(Year([Forms!frmDateSelector!txtStartDate]),12,25))=5 Or Weekday(DateSerial(Year([Forms!frmDateSelector!txtEnd Date]),1,1))=5,51,52))

The trouble lies in the fact that the club's fiscal year runs from October 1 through September 30, and their meetings are always on Thursdays. Since all years are not created equally, Oct 1/08 through Sep 30/09 has 50 weeks in which meetings are held, and Oct 1/07 through Sep 30/08 has 52.

All of this has to do with finding some proper method to calculate Perfect Attendance...which has a separate wrinkle of its own, in that any 12 consecutive months of fulfilling the required number of meetings yields perfect attendance for that year.

Tom

 

[dhookom]

I would build a table of all meeting dates. Then, you could count the number of meeting dates between two dates very easily.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Is there a way to build that table, automatically adding to it as time goes on, without having to enter each of the dates manually?

Tom

 

[dhookom]

I would probably create the table in Excel and then copy it into an Access table. It shouldn't take 5 minutes.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Hey...great idea!!

Thanks.

Tom

 

[THWatson]

Duane
How about this as an alternative to building a table? It uses the same method as you suggested for getting the # of Thursdays in the selected period.

On the form, frmDateSelector, there are 2 text boxes in which the user plugs the date parameters, txtStartDate and txtEndDate.

Put 3 additional text boxes - txtStart, txtEnd, txtWeeks.

txtStart has the control source

=IIf(Month([txtStartDate])>=10,DateSerial(Year([txtStartDate]),10,1),DateSerial(Year([txtStartDate])-1,10,1))

txtEnd has the control source

=IIf(Month([txtEndDate])>=10,DateSerial(Year([txtEndDate])+1,9,30),DateSerial(Year([txtEndDate]),9,30))

txtWeeks has the control source

=RetThur([txtStart],[txtEnd])

Tom

 

[dhookom]

I would just build a table and use SQL.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Thanks, Duane.

Is there a reason for preferring one method over the other?

Tom

 

[dhookom]

SQL will almost always perform more efficiently. Also, you should try to maintain records/data rather than code.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Thanks!

Tom

 

[THWatson]

Duane
I don't mean to prolong this, and if I'm bugging you please just ignore this and don't respond. Just chalk it up to old age, thick head, or something.

However, your first reply and your recent "you should try to maintain records/data rather than code" leave me a little puzzled.

In both cases I am just taking existing stored data and manipulating it by code. So if it's wrong to do the second one through a text box in a form, then it should be wrong to do the first one through a text box in a form...no?

Tom

 

[dhookom]

At one point, you added "excluding Christmas and New Years when those days fall on a Thursday" which suggested you would need to add code to determine when these holidays fall. I expect your specs will continue to change as you find more exceptions to counting "all Thursdays".

Generally I would expect you might already have a table with a record for each meeting. My suggestion is to get rid of much of the code and replace it with queries against the tables.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks for your reply. I appreciate it.

The only other OFFICIAL holiday that can fall on a Thursday is July 1, Canada Day - similar to the U.S. 4th of July.

Other holidays in the year...for example...are always observed on the Monday preceding them. The OFFICIAL July 1 is always on the actual day, so government offices and banks close on that day; however, all offices and factories etc. follow the same pattern that is followed for the other statutory holidays throughout the year and give their employees the preceding Monday off.

This Kiwanis club, however, holds a Regular meeting on Thursday even if it's July 1st. It is only when Christmas or New Years falls on Thursday that they don't meet in that month.

However, I finally get your point through my head. Who knows at what point more exceptions might creep in there and then this would have to be re-done. Much better to do it now and avoid future maintenance!!

I do have a table with a record for each meeting.

There are a few parts to this database creating extra thought which keeps me awake at night...
1. The club's Fiscal year runs from October 1 of one year through September 30 of the following year.
2. This Fiscal year setup means that there can be either 50 or 52 Thursdays upon which meetings are held. (Or if the user selects a period of time other than what fall within a natural Fiscal year, there could be 51 Thursdays (e.g. January 1, 2008 through December 31, 2008).
3. Perfect Attendance is another interesting issue, and the most complex. If a member has perfect attendance in ANY given 12 month period, he/she is granted perfect attendance. (if it followed the Fiscal year it would be easier to calculate)
4. Perfect Attendance for a month is counted as the total of Regular Meetings and Makeup Activities. (there are 23 Makeup Activities currently). Therefore a Member must have a meeting total of 3 in a 3 Thursday month, 4 in a 4 Thursday month, and 5 in a 5 Thursday month.
5. So if a member hasn't attended sufficient Regular meetings, he/she can make up by doing Makeup Activities.
5b. However, the member's total of Regular + Makeups cannot exceed the sum of 3 in a 3 Thursday month, 4 in a 4 Thursday month, and 5 in a 5 Thursday month. AND they cannot bank ahead or use from behind. But, theoretically, the member could attend NO Regular meetings in a given month and still get perfect attendance by making up enough through Activities...with the exception that no more than 4 Makeups can be used, so if there are 5 eligible Thursdays in a month the member must attend at least 1 Regular meeting.

This may help to explain, at least a little, why I have been working on these RetThur functions to get the eligible Thursdays in a month, and the WeekCount to get the # of weeks in the fiscal year's cycle.

The Perfect Attendance is the last piece I have to work out, and since every member in the club can be working on a different 12 month cycle that's been the bone that has taken the biggest chewing...giving my tired old false chompers quite a workout.

Best regards, Duane. Thanks for listening. And I finally figured out the proper formula for getting the number of weeks in a fiscal year in Excel. That took a while as I don't do much work in Excel at all.

[COLOR=blue]Assuming that B2 is the StartDate of a Fiscal Year, and C2 is the end date of a Fiscal year, the code is[/color]
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)))=5))-IF(WEEKDAY("1/1/" &YEAR(C3))=5,2,0)

Tom

 

[THWatson]

Sorry, that should have been
A2 as the Start Date and B2 as the End Date, and the formula in C2.

But here's an interesting wrinkle. If you use the dates between Oct. 1, 1975 and September 30, 1976 The formula yields 51 Thursdays...but there are actually 52 by manual count.

Tom

 

[dhookom]

Quite often you can take the same function you created in Access and use it in Excel.

Your perfect attendance is interesting. Apparently months are critical since you can't bank attendance between months. You should be able to query the number of meetings per month and count of attendance per month by member. At this point, you should probably tell us how you intend to record attendance since we can't see your tables ;-)

Duane
Hook'D on Access
MS Access MVP