Results 1 to 7 of 7
  1. #1
    cnorris is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    2

    How can I get Yesterday's date only for Working Days?

    Hi,

    In Excel I'm able to use =workday(now(),-1) and would like to use similar in Access- can anyone help please?



    Thanks, Carl

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    This should work in a query or form calculation

    Expr1: Weekday(Date()-1)

  3. #3
    cnorris is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    2
    Hi, thanks for your reply. I am trying to have Access automatically look to yesterday's activity, but since we're not open Saturday or Sunday (but have orders placed via our Website) I need to look to Friday hence my need to look for Workday as I'm able to in Excel.
    Unfortunately Weekday returns the integer 5 for today.

    Thanks, Carl

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  5. #5
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Carl, I too just recently had a request to have a report that showed the past 3 'business' days, so I know what you are going through.

    For our purposes, I just decided for the time being to live with the 3 days; regardless of weekend or holiday. If you however need to proceed further, here is a link to my post and a suggestion given to find the previous days as the TOP days in the sort. I liked the idea as opposed to programatically creating something to run through.

    Here is the link;

    https://www.accessforums.net/queries...day-28723.html

    Hope that also helps get you going in the right direction for your case.

    Tim

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Quote Originally Posted by alansidman View Post
    This should work in a query or form calculation

    Expr1: Weekday(Date()-1)
    Alan's is the way to go with a slight modification:

    Expr1: dateadd("d", (iif(weekday(date()) between 3 and 7, -1, iif(weekday(date()) = 1, -2, iif(weekday(date()) = 2, -3, null)))),date())

    if you're looking for the most recent work date, if it's a monday, you want to subtract three days (weekday for monday = 2).
    if it's a sunday, you want to subtract 2 days (weekday for sunday is 1), any other day you want to just subtract one day.

    Then to get the actual date you subtract (using the dateadd function) the calculated days from the current date.

  7. #7
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    I have most of my paperwork and workload down to a small level; so I decided to see about making a 'business weekday' type of line, using what 'rpeare' posted above. With the one I was given a few days ago, and the one above; I can whip something up for that query.

    However, I just want to forewarn anyone when testing on a 'TEST DATABASE', that sometimes in it not the code that is not working, it is the test database not having the correct data to work with. Using the real database, I accepted some parts into receiving, so I know I have data to work with. Every test I did on the test database kept coming back incorrect. It would not display the damn parts I know I got today. Why won't this damn thing work as I know these parts are here and the code looks right!

    Well Tim, maybe it is because you are working on the test database (duplicate database as of yesterday), that does not have today's data to play with.


    Lesson learned. Your coding may be correct and all tests show something is wrong. Maybe you have to step back for a minute and make sure all is normal before proceeding. I couldn't believe I tried all that time testing and no data would come up for today. It's not me coding incorrectly, it is me not working with the correct data for testing.

    Have a good weekend everyone!

    Tim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-24-2012, 02:34 PM
  2. Working days module help for noob
    By AndycompanyZ in forum Modules
    Replies: 5
    Last Post: 07-27-2011, 01:43 AM
  3. codes for working days error
    By Harry in forum Programming
    Replies: 21
    Last Post: 10-15-2010, 02:32 PM
  4. calculate no. of working days
    By JOSE LUIS in forum Access
    Replies: 1
    Last Post: 02-01-2010, 03:55 PM
  5. Replies: 2
    Last Post: 07-31-2009, 06:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums