Thread: function to get number of required Thursdays in month

1. I have a table, tblMonths, that has 2 fields - "MonthYear" and "Required"

This table was built from an Excel file. It has the Month and Year of a large number of years (e.g. January 2000, February 2000 etc.)

The Required field has to do with the fact that there could be 3, 4 or 5 required Thursdays in the selected month.

How do I write a function to handle that, dependent upon the Date entered.

Perhaps something such as getNumberMeetings(dtmDate as date) as integer

Thanks.

Tom

2. You can use

DLookup("Required", "tblMonths", "TheDate=#" & Format([dtmDate]-Day([dtmDate])+1, "mm/dd/yyyy") & "#")

If the date supplied (dtmDate) is always the first of the month, you can simplify this to

DLookup("Required", "tblMonths", "TheDate=#" & Format([dtmDate], "mm/dd/yyyy") & "#")

3. Hans
I'm not sure the DLookup function will be of benefit. I already have a function that will give me the correct # of Thursdays. It's in a module
[codebox]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 = "Null"
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[/codebox]
I'm still wrestling with the Perfect Attendance portion of this database, and feel as if I'm swimming upstream against a current that I can't possibly manage. If I can get that piece to work I will be able to finish this off, but so far I'm drowning.

I know the amount of work you went to when you provided me with all those queries that eventually produced a collection of perfect attendance calculations, and you will recall you said it ran slow. Well, it actually not only ran slow, it conked out for a lack of memory once there was 2 year's worth of data with which to work...and I have 4 meg of ram. The Kiwanis friend, for whom I am developing this database, has a computer with only 1 meg of ram, so it would choke more quickly on his machine.

Tom

4. [quote name='THWatson' post='769488' date='07-Apr-2009 14:22']Hans
I'm not sure the DLookup function will be of benefit. I already have a function that will give me the correct # of Thursdays. It's in a module
[codebox]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 = "Null"
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[/codebox]
I'm still wrestling with the Perfect Attendance portion of this database, and feel as if I'm swimming upstream against a current that I can't possibly manage. If I can get that piece to work I will be able to finish this off, but so far I'm drowning.

I know the amount of work you went to when you provided me with all those queries that eventually produced a collection of perfect attendance calculations, and you will recall you said it ran slow. Well, it actually not only ran slow, it conked out for a lack of memory once there was 2 year's worth of data with which to work...and I have 4 meg of ram. The Kiwanis friend, for whom I am developing this database, has a computer with only 1 meg of ram, so it would choke more quickly on his machine.

Tom[/quote]
Does the start and end date have to be in the same month?
if yes, then try this:
(Day(toDate) - (5 - Weekday(fromDate) + 1) + IIf((5 - Weekday(fromDate) + 1) < 1, 7, 0)) \ 7 + 1

5. Getting the number of required Thursdays in a month is not the part that causes the slowness. It's the bizarre set of rules, requiring enormous amounts of data to be compared.

6. [quote name='HansV' post='769497' date='07-Apr-2009 06:09']Getting the number of required Thursdays in a month is not the part that causes the slowness. It's the bizarre set of rules, requiring enormous amounts of data to be compared.[/quote]
Don't I know it!

The bizarre set of rules is akin to the Canadian tax code!

This sucker is wearing me down. Good thing Easter's coming.

Tom

7. I am attaching a stripped down, zipped, copy of the database, using only the objects that pertain to this perfect attendance problem.

The query currently called zzzz shows the members with perfect attendance in the past 12 months. For example, use the parameters from 10/1/07 to 9/30/08

I would appreciate any thoughts.

Tom

8. So what exactly is your question this time?

9. [quote name='HansV' post='769550' date='07-Apr-2009 15:40']So what exactly is your question this time? [/quote]
Hans
Sorry to see that little icon fella scratching his head.

My question was a simple one. It had to do with the process.
Does the process appear acceptable and workable over the long haul?

Tom

10. As far as I can tell, your queries will work to decide who had perfect attendance during a single month.
But if you specify a date range spanning more than one month, the queries will lump all Thursdays in the date range together. So you can't use them to decide whether someone had perfect attendance within each of the months in the date range.

11. [quote name='HansV' post='769559' date='07-Apr-2009 16:14']As far as I can tell, your queries will work to decide who had perfect attendance during a single month.
But if you specify a date range spanning more than one month, the queries will lump all Thursdays in the date range together. So you can't use them to decide whether someone had perfect attendance within each of the months in the date range.[/quote]
Thanks, Hans. Your wisdom is appreciated.

I'll have to look at it more closely.

Tom

12. Well, having manually checked each one of those members who showed up in the zzzz query when I ran it, all seem correct.

Tom

13. [quote name='THWatson' post='769587' date='07-Apr-2009 19:31']Well, having manually checked each one of those members who showed up in the zzzz query when I ran it, all seem correct.

Tom[/quote]
Hans
I was wrong! My results are far from correct. In fact, there are only 2 members with perfect attendance currently. It has to do with, as you suggested, my grouping of data.

Don't worry about it further. I've wasted enough of your time already, and you have really tried to be helpful and I greatly appreciate it.

Tom

Posting Permissions

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