Previous Business Day in Access Query

[cmcreynolds]

Hello,

I've been asked to retrieve records from our account management system that were created the previous business day. I am used to PowerQuery's date tool, but the syntax doesn't seem to jive (understandably so). (Like =Today()-1 or similar)

My only objective is to save me some time by entering yesterday's date every time I run the query.

Any suggestions?

 

[xenou]

Date()-1


But you must be careful about "created yesterday".
If dates are stored as datetimes beware.

 

[cmcreynolds]

So, Date() is "today's date"? Neat.

I suppose now, the question becomes: Is there a way to "sift through" it being the last business day? For example, for today, 12/5/2016, I'd like to pull from 12/2/2016. I know that there are some nifty business day calculations built into Excel and PowerPivot (unless I'm misremembering?), wasn't sure what it'd be in MS Access.

 

[cmcreynolds]

I'm looking at records that were produced by our customer service team, who are not in the office on weekends, so I just need working days.

 

[xenou]

That is tricky and usually I don't offer advice because of the difficulty of dealing with holidays. You need to write a function and you need to store a holiday schedule. And you need to keep that schedule updated.

My solution is to keep a table of dates in my database with all the information I need about those dates. Then I just pull from the table. For instance, is it a working day? Which working day of the month is it? How many shifts work on that day? What is the end of the month for that day's month . And so on ... whatever data is relevant to that particular day. Actually going this route you don't need any functions - just include your dates table in the joins.

I'm afraid I don't of anything nifty you can use. Maybe somebody else has that information

 

[cmcreynolds]

Well, but I suppose that's practical and feasible within my Access understanding limits. Thank you, xenou.

 

[Joe4]

People have created their own User Defined Function in Access to do the same things that Workdays and Networkdays does in Excel. You can find them with a simple Google Search.
Here is one: http://access.mvps.org/access/datetime/date0012.htm

 

[xenou]

If you use my table based solution I have always found it convenient to build my initial data in Excel since it is easy to create a list of many years worth of dates in a few minutes -- for instance, just dragging down to populate dates, then using formulas as needed to fill in other fields such as day of week, end of month, days in month, and so on. For holidays, I use an IsHoliday column with checkboxes. Each year I just go in and check off the holidays.