I am looking to (in an Access query) identify if a date in a table is either the 1st, 2nd, 3rd, 4th, or 5th Thursday of that month..i guess I would need 5 formulas for this..  for example..

1/6/05 would be the 1st Thursday in January and...

12/15/05 would be the 2nd Thursday in December...

It would like the solution to have this type of result:

Table:
--------
Date
--------
1/6/05
--------

~~~

Query:
--------
IIf(table.date = [1st thursday], 1, 0)

... this would return a 1 because it's true.

Hi,
The following function works in VB6 and Access 2003 (so, I assume it works in access 2002 too):

Option Compare Database

Public Function GetCountInMonth(dt As Date, Optional WhatDay As VbDayOfWeek = vbThursday) As Integer
    ' Return the week number of the month, that this date occurs on
    Dim dtFirst As Date
    Dim intOccurence As Integer
    
    ' This is not the same day of the week, so exit
    If Weekday(dt) <> WhatDay Then Exit Function
    
    ' get the first occurence in the month
    dtFirst = CDate("01 " & Format(dt, "mmm") & " " & Year(dt))
    While Weekday(dtFirst) <> WhatDay
        dtFirst = dtFirst + 1
    Wend
      
    ' Add 7 days to this until we get a next month, or > dt
    
    While dtFirst <= dt
        intOccurence = intOccurence + 1
        dtFirst = DateAdd("ww", 1, dtFirst)
    Wend
    
    GetCountInMonth = intOccurence
End Function


Usage:
Parameter 1 is the date you want to check
Parameter 2 is the weekday you want to compare it with

ie.
to find if the date is the first thursday, then:
iif ( GetCountInMonth(table.date, vbThursday ) = 1, 1 ,0)

to find if the date is the 4th wednesday:
iif ( GetCountInMonth(table.date, vbWednesday ) = 4, 1 ,0)

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)