Results 1 to 7 of 7

Thread: date problem

  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: date problem

    i am looking to find the first thursday of the year and the last thursday to do an end of year report query

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Something along these lines should work:

    Code:
    Function FirstThursday() as Date
    DIM dtmStart as Date
    DIM x as integer
    dtmStart = Cdate("01/01/" & Year(Now)
    For X = 0 to 6 'you only need to loop thru the first 6 days
       dtmStart = DATEADD("d",X,dtmStart)
       If DATEPART("w", dtmStart) = vbThursday Then 
          FirstThursday = dtmStart
          Exit For
       End If
    Next X
    Exit Function
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2004
    Posts
    178
    can i put that into a query

  4. #4
    Join Date
    Jun 2004
    Posts
    92
    Yes, you can do something like:

    Code:
    Fieldname : FirstThursday()
    Put this in the "field" box in the query column, changing fieldname to whatever you want the column name to be.

    You'll have to change the line "Function FirstThursday() as Date" to "Public Function FirstThursday() as Date" and put the function in a module.

  5. #5
    Join Date
    Aug 2004
    Posts
    178
    i was hoping to do something like

    between firstthursday()-6 and lastfriday()

    in the criteria of datesold field

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Here's another function without using a loop. You can change the day parameter to use other days besides Thursday.
    Code:
    ' Use with vbDay or Day #
    ' n = FirstDay(2005, vbThursday)
    ' n = FirstDay(2005, 5)
    
    Function FirstDay(nYear, vbDay)
      calcDay = 8 - Weekday(DateSerial(nYear, 1, 1)) + vbDay
      If calcDay > 7 Then
        calcDay = calcDay - 7
      End If
      dayName = WeekdayName(1, False, vbDay)
      
      strMsg = "The First " & dayName & " of the year " & nYear & _
                " will fall on " & DateSerial(nYear, 1, calcDay)
      FirstDay = strMsg
    End Function
    Constant Value Description:
    vbUseSystem 0 Use the NLS API setting.
    vbSunday, 1, Sunday
    vbMonday, 2, Monday
    vbTuesday, 3, Tuesday
    vbWednesday, 4, Wednesday
    vbThursday, 5, Thursday
    vbFriday, 6, Friday
    vbSaturday, 7, Saturday
    ~

    Bill

  7. #7
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by mega1
    i was hoping to do something like

    between firstthursday()-6 and lastfriday()

    in the criteria of datesold field
    You can do:

    Code:
    between dateadd("d",-6,firstthursday()) and lastfriday()
    Additionally the code provided to you was broken. The following is what I used. Just put it in a module:

    Code:
    Public Function FirstThursday() As Date
    Dim dtmStart As Date
    Dim x As Integer
    Dim dtmdate As Date
    dtmStart = CDate("01/01/" & Year(Now))
    For x = 0 To 7 'you only need to loop thru the first 6 days
       dtmdate = DateAdd("d", x, dtmStart)
       If DatePart("w", dtmdate) = 5 Then
          FirstThursday = dtmdate
          Exit For
       End If
    Next x
    End Function

Posting Permissions

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