Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Question Unanswered: Last working day in Last year.

    How do i find out the last working day for the previous year? For Eg; I want to get the last working day for the year 2002, ie 31/12/2002. (I need this date as a field in a form) Any help highly appreciated.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Through a function that checks the weekday of (31/12/??) then make a SELECT statement
    if it is vbSunday then Last worked day = 29/12/??
    if it is vbSaturday then Last worked day = 30/12/??
    CASE ELSE
    Last worked day = 31/12/??
    Hope that what you need

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    You are right. Since i am not that familiar with creating functions, i was looking for a formula (I forget to mention it in my previous post) to sort it out. I tried little bit to create a function for that, unfortunately it was unsuccessfull. Thanks for you help.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Last working day in Last year.

    Originally posted by macb
    How do i find out the last working day for the previous year? For Eg; I want to get the last working day for the year 2002, ie 31/12/2002. (I need this date as a field in a form) Any help highly appreciated.
    This is pretty rough and some may laugh but I just wrote a quick function that can be used in a query. I returned the last date that was a weekday, based on the day that was input in the function.

    Public Function GetDate(NewDate As String) As String
    Dim strDate As String, x As Integer, iday As Integer
    strDate = NewDate
    For x = 1 To 7
    iday = Weekday(DateValue(strDate))

    If iday > 1 And iday < 7 Then
    GetDate = strDate
    Exit Function
    End If
    strDate = CStr(DateValue(strDate) - 1)

    Next
    End Function

    In a query column: LastWeekday: GetDate("12/31/02")

    Good luck.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Thanks for the reply. But I can't work out your function. I am not getting what I am exactly looking for. If my question is not clear, i will try to make it little more clear.

    For Eg. On a user form I have a txtBox1 with today's date. On the same form in another txtbox2 i need to get the date of last years last working day. (ie. if I have Nov. 04, 2003 in txtBox1, i want to get 31/12/2002 (as date not string) in txtbox2.

    Thanks and any help highly appreciated.

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Originally posted by macb
    Thanks for the reply. But I can't work out your function. I am not getting what I am exactly looking for. If my question is not clear, i will try to make it little more clear.

    For Eg. On a user form I have a txtBox1 with today's date. On the same form in another txtbox2 i need to get the date of last years last working day. (ie. if I have Nov. 04, 2003 in txtBox1, i want to get 31/12/2002 (as date not string) in txtbox2.

    Thanks and any help highly appreciated.

    As TextBox1 Exit event do the following:

    Private Sub Text1_Exit(Cancel As Integer)
    Dim PrevYear as integer

    prevYear= Year(CDATE(Text1))-1

    textBox2= DateSerial(PrevYear,12,31)
    END

    But this will give you always 31/12/PrevYear
    Hope that is what you need
    [/COLOR]

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    Thanks. With slight changes i have done it.

Posting Permissions

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