Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So what exactly is your question this time?

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
  •