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
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
This should work in a query or form calculation
Expr1: Weekday(Date()-1)
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
Here are two items to look at.
http://www.techonthenet.com/access/f...eekdayname.php
http://www.techonthenet.com/access/f...te/weekday.php
That should help you tweak it.
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
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.
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