Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Unanswered: how to do Excel's =workdays(now(),-1) in Access 97

    Hello,
    I have a form where I used to use now() default value. Just today I found out that the default value should be the previous workday. We all know that Access 97 doesn't have a Workdays function. How do I create a default value to be the previous workday?

    In Excel it used to be WORKDAYS (today(), -1, HOLIDAYS)

  2. #2
    Join Date
    Jan 2003
    Location
    Dallas/Houston
    Posts
    7

    Try this functions

    Weekday(now())

    it will give you the number from 1 to 7 representing the day of the week.

    If you leave it this way the day 1 will be sunday, day 2 monday, etc, etc
    hope it works

  3. #3
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    this reminds me when I was trying to find Calendar Software, which allows to reschedule recurrence from weekends.... only LOTUS NOTES can do it!

    check this...


    Public Function PreviousWorkDay(InputDate As Date) As Date

    Select Case Weekday(InputDate)
    Case 1
    intMove = -2
    Case 2
    intMove = -3
    Case Else
    intMove = -1
    End Select
    PreviousWorkDay = DateAdd("d", intMove, InputDate)

    End Function


    jiri

  4. #4
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Understood, thank you, just one clarificiation. Obviously I want to implement this procedure to show me a default value in a Text Box. Where would I write the code. Or in other words, how do I modify the code below? Thanks Jiri

    Originally posted by playernovis
    this reminds me when I was trying to find Calendar Software, which allows to reschedule recurrence from weekends.... only LOTUS NOTES can do it!

    check this...


    Public Function PreviousWorkDay(InputDate As Date) As Date

    Select Case Weekday(InputDate)
    Case 1
    intMove = -2
    Case 2
    intMove = -3
    Case Else
    intMove = -1
    End Select
    PreviousWorkDay = DateAdd("d", intMove, InputDate)

    End Function


    jiri

  5. #5
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    put to your textbox to DEFAULT VALUE =PreviousWeekDay(Now())

    you can add FORMAT if you want just Date..
    for example =Format(PreviousWeekDay(Now()),"mm/dd/yyyy")





    jiri

  6. #6
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    I don't know if I'm just slow or something. I have it all in place and still get an error. Here is the picture. Anybody catches the error why it's not working?
    Attached Thumbnails Attached Thumbnails dbday-1.jpg  

  7. #7
    Join Date
    Oct 2002
    Location
    UK
    Posts
    10
    Just a thought...
    I notice from that picture that you have a field in the table called 'DATE'.
    I have had problems in the past with fields called Date so i give them another name, like TaskDate, ItemDate etc...
    Thinking about it, that may only be a problem when working with Access and ASP pages, but it may be worth a try???

    Tim Alston
    Gamalan Media Group

  8. #8
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126


    Thanks for all the help. Actually the DATE wasn't a problem. There was just one undeclared variable (intMove) which it couldn't work without. Now it works just awesome. Thanks Jiri

Posting Permissions

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