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

 

[THWatson]

Duane
There is a tblMembers and a tblAttendance

Regular Attendance is created through a form by selecting
1. The Meeting date
2. The members who attendance that date by clicking names in a List Box

Makeup Attendance is created through a form by selecting
1. the Makeup date
2. the Makeup Activity
There is a list box of Members, but since there are 23 Makeup Activities, recoding of them is done individually by member (not often that 2 people do the same Makeup Activitity on the same date, and won't do the same # of hours at the event)

Fields in tblAttendance are
AttendanceID (autonumber, PK)
MemberID (number, FK to tblMembers.MemberID)
MeetingDate (date)
Present (Yes/No)
HoursSpent (# of hours spent at Makeup Activity...solely for purposes of reporting to Kiwanis International)
MakeupID (FK to tblMakeups.MakeupID, 1 to 23, depending on the Makeup Activity)
MeetingTypeID (FK to tblMeetingType.MeetingTypeID, at the moment can be with 1 for Regular Meeting, or 2 for Makeup)

Does that help? Or do you want me to post something in some public space?

Tom

 

[dhookom]

You should be able to create a query of the number of attendences per month like:

SELECT MemberID, Year(MeetingDate) as Yr, Month(MeetingDate) as Mth
FROM tblAttendance
GROUP BY MemberID, Year(MeetingDate), Month(MeetingDate);

You can create a similar query that groups the meetings table by year and month. Combining these should get you started on figuring perfect attendance.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
That appears to be a push in the right direction.

I'll keep working with it.

THANKS!

Tom

 

[THWatson]

Duane
The formula I worked out to get the # of Thursdays in a year, excluding Christmas and New years, is producing results that are not accurate.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)))=5))-IF(WEEKDAY("1/1/" &YEAR(C3))=5,2,0)

As I indicated earlier, 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.

Also, I now see that the result the formula yields for between Oct. 1, 1992 and Sept. 30, 1993 is 53 Thursdays, whereas there are only 52.

I realize this is beyond the scope of this forum, but do you know how to tweak this formula so that it works correctly?

Tom

 

[THWatson]

I can, however, just bring the Start Date and End Date values in Excel into an Access table, and then use the RetThur module in a query to get the proper # of Thursdays in the fiscal year.

Tom

 

[dhookom]

If the function RetThur() worked in Access, just use it in Excel. Open a new module in Excel and paste the function in. Then in a cell in your worksheet enter:
=RetThur(B2,C2)


Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Ah...good...I'll try that.

Tom

 

[THWatson]

Duane
This perfect attendance, with its bizarre set of rules, is driving me nuts. I have spent so much time on it that it's reached the ridiculous point.

If I could just use the fiscal year for the club as the period of time within which to calculate perfect attendance it would be simpler, but tain't the case. Each member can be working on their own 12 month perfect attendance cycle.

Using the Select query you suggested, and adding the "Having" line, as a template

SELECT tblAttendance.MemberID, Year(MeetingDate) AS Yr, Month(MeetingDate) AS Mth, First(tblAttendance.MeetingDate) AS FirstOfMeetingDate
FROM tblAttendance
GROUP BY tblAttendance.MemberID, Year(MeetingDate), Month(MeetingDate)
HAVING (First(tblAttendance.MeetingDate) Between #10/1/2008# And #9/30/2009#));

how do I write a function that would give me the number of meetings a member attended, by plugging in the MemberID for that member?

Where I am heading is this:
I want to be able to figure out who had perfect attendance this month, put a counter of 1 in a table for each of those. Next month, figure out who had perfect attendance, then if the member had perfect 2 months in a row, augment their counter by 1, else reset their counter to 0.

Does that make sense? Not to be overly dramatic, something has to in this mess!

Tom

 

[THWatson]

Duane
I have built 5 queries, and the final one will give me those members who have perfect attendance in a given time period.

So it would appear that the following sql would tell me, at any point, the names of members who had perfect attendance in the last 12 months.

SELECT yyyPerfectForMonthSelected.MemberID, yyyPerfectForMonthSelected.FullName, Count(yyyPerfectForMonthSelected.OKAY) AS CountOfOKAY
FROM yyyPerfectForMonthSelected
GROUP BY yyyPerfectForMonthSelected.MemberID, yyyPerfectForMonthSelected.FullName
HAVING (((Count(yyyPerfectForMonthSelected.OKAY))=12));

I know that it's difficult to see when you don't see the other 5 queries upon which this 6th one is built. But, as a concept, any thoughts?

Tom

 

[dhookom]

Looks great from my perspective.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
To use a Lenten expression, it has felt to me that I have been slowly plodding toward Good Friday for about 3 months on this one, but maybe Easter looms?

Tom

 

[THWatson]

Duane
Don't feel compelled to look at this, but if you're interested, here's a link to where I stored a stripped down, zipped, copy of the db, using only the relevant objects.

The query currently named zzzz is the one that gives those who have perfect attendance in the last 12 month cycle.

http://www.4shared.com/file/97235237/699e37/Store_Tom.html

Tom

 

[dhookom]

By your calculations, who would have had perfect attendance in which months? Does this look accurate?
[tt]
Last First ID MthStart
======= ======== == ==========
Hoffman Fred 12 9/1/2008
Hoffman Fred 12 10/1/2008
Hoffman Fred 12 11/1/2008
Hoffman Fred 12 12/1/2008
Hoffman Fred 12 1/1/2009
Hoffman Fred 12 2/1/2009
Hoffman Fred 12 3/1/2009
Sloan Douglas 37 12/1/2008
Sloan Douglas 37 1/1/2009
Sloan Douglas 37 2/1/2009
Sloan Douglas 37 3/1/2009
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
No. Using the time frame of 10/1/07 to 9/30/08, I would have perfect attendance (12 consecutive months meeting the requirements) as follows...
MemberID FullName CountOfOKAY
1 Anderson, Peter 12
3 Bedard, Chris 12
4 Bisson, Chris 12
5 Brandon, Ron 12
11 Green, Bill 12
12 Hoffman, Fred 12
18 Marshall, Jim 12
22 McSherry, Peter 12
23 Mizen, Wayne 12
37 Sloan, Doug 12
38 Smith, Larry 12
41 Steffler, Mike 12
62 Carkner, Patty 12
75 Oosterhuis, John 12

Tom

 

[dhookom]

I think the issue between our differences is your table structure that might have two attendances in a single record. I would not have structured the attendance table like yours. I would expect that a single "attendance" should create a single record whether or not it was regular meeting or makeup.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
In tblAttendance, a single attendance record does create a single record. If it's a Regular meeting record, it is a single record, with a MeetingTypeID = 1. If it's a Makeup meeting record, it is a single record, with a MeetingTypeID = 2.

The Regular and Makeup meetings are brought together in a Union query, for a particular member.

The tblAttendance is structured thus:
AttendanceID: auto number, PK
MemberID: Number, FK to MemberID PK in tblMembers
MeetingDate: Date/Time
Present: Yes/No
HoursSpent: Number (but not relevant for Perfect Attendance)
MakeUpID: Number, FK to MakeUpID PK in tblMakeups
MeetingTypeID: Number, FK to MeetingTypeID in tblMeetingType

I'm not clear how it appears that I have 2 attendances in a single record.

Tom

 

[THWatson]

Duane
Actually, the further I check this out, your perfect attendance data shown below is correct.
Last First ID MthStart
======= ======== == ==========
Hoffman Fred 12 9/1/2008
Hoffman Fred 12 10/1/2008
Hoffman Fred 12 11/1/2008
Hoffman Fred 12 12/1/2008
Hoffman Fred 12 1/1/2009
Hoffman Fred 12 2/1/2009
Hoffman Fred 12 3/1/2009
Sloan Douglas 37 12/1/2008
Sloan Douglas 37 1/1/2009
Sloan Douglas 37 2/1/2009
Sloan Douglas 37 3/1/2009

There would be others who met the requirements in individual months. One, for example MemberID 38, would be perfect for the past 8 months.

Tom

 

[THWatson]

Duane
Don't spend a bunch of time on this. I'm working with MajP, over in the Forms forum, on a process that might just be getting us closer. So I don't want to waste your time on it. Heaven's sakes, I've wasted enough of my own!!

Tom