# Access query to retrieve records from the previous business day

I need to create one query to run every day , using Microsoft Access that finds records from the previous work day. Tues to Friday, the previous work day is date()- 1. Simple enough.  But on Monday, if I use date()-1, it will miss records from that previous Friday or Saturday.   its an iif statement  but I need help with the rest please.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

I don't use Access, but the basic concept should be the same. If the days of week are 1-Sunday, 2-Monday, etc...

When Day of Week today is 2  (Monday) Then Deduct 3 days ==> Friday
When Day of Week today is 1  (Sunday) Then Deduct 2 days ==> Friday
For all other days (Tuesday-Saturday) Deduct 1 day

So calculate the number of days (-1, -2 or -3) and subtract that number from today's date to get the previous work day.  In psuedo code, something like this:

daysToDeduct = IIF(WEEKDAY(NOW())=2,-3,IIF(WEEKDAY(NOW()) = 1, -2, -1))
...
WHERE DateColumn = DateAdd("d", -DaysToDeduct, Date())
0
Commented:
You can construct a lookup table of values with the Choose() function.  You can play with this in the immediate window of any office product.
``````vweekday = vbsaturday
?vweekday,choose(vweekday,vbfriday,vbfriday,vbmonday,vbtuesday,vbwednesday,vbthursday,vbfriday)
``````

Since the VB intrinsic constants aren't known in to the query engine, your actual SQL would look more like this:
``````Choose([vweekday], 6, 6, 1, 2, 3, 4, 5)
``````
where [vweekday] represents the Weekday([datecolumn]) value
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
some discussions available in this PAQ, including to handle public holidays

How do I determine the previous business day in MS Access ?
0
Commented:
You never said what you want to do with the result of this query so it is hard to tell you what might be the best solution.

To handle holidays (without maintaining tables) and missed days (you're sick) - Create a log table.  At the end of running this process, Your code adds the run date to the log table.  In the criteria for the query, use
Where SomeDate > DMax("LastRunDate", "tblRunLog")
0
Commented:
IF you can make the assumption that every "Work Date" will have at least some records in your table, you can get by with this (you'd have to adjust accordingly if this is a date/time field):

``````SELECT * FROM YourTable WHERE DateField IN (SELECT MAX(DateField) FROM YourTable WHERE DateField < Date())
``````
0
Commented:
This will do it if you are also tracking time:

``````SELECT * FROM tblStuff
WHERE INT(DateTimeField) IN (SELECT MAX(Int(DateTimeField)) FROM tblStuff WHERE DateTimeField < Date())
``````
0
AnalystAuthor Commented:
To explain how the data is being used;
I am appending a table called " Daily Production" every day from a table of task.The date being retrieved is the date tasks are completed.
I have the following in the query:

which works well except it excludes Saturday.  it is rare but tasks do get completed on Saturday.
0
Commented:
If you create a macro that runs this query, you can create a .bat file to run the database and run the query.  The criteria should be changed to:
Completed_DT = Date()-1

Then use Windows scheduler to schedule the .bat as a daily task.  Pick some time between 1 and 6 AM each day.  There is no point to attempt to skip any days.  If work was done, you want to capture it.  So don't worry about Sunday or holidays because you think work will "never" be done on those days.  If work IS done, you want to capture it.  If work isn't done, the query doesn't do anything.
0
Commented:
The criteria I gave you earlier should also work, by returning records from the last (previous) day any work was recorded, regardless of whether it was yesterday, Saturday, or a Thursday before a holiday:

``````WHERE INT(Completed_DT) IN (SELECT MAX(Int(Completed_DT )) FROM tblStuff WHERE Completed_DT < Date())
``````
0
AnalystAuthor Commented:
0
AnalystAuthor Commented:
0
Commented:
If you don't want to do what I suggested (a simple query that runs every day because you created a Windows task to run it), then use the query suggested by  mbizup.  Both will handle missing a day of running the query so that you don't ever loose data.  That can't be said for the solution you like.

You posted two queries - or the same query twice, I didn't look closely enough to determine but never said what was wrong with them.
0
Commented:
Using the Choose() function in your Where clause:
``````WHERE ([All Tasks].[COMPLETED_DT] =
Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1) )
Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1) );
``````
0
Commented:
Sorry aikimark but this solution will fail if the user misses a day of manually running the query.

Once we found out that the objective is to pick up items from the previous run day, the solution changes.  It doesn't matter what today is or what the previous day is.  All that matters is that we select items since the last time the process ran.  My solution was to use a batch file so we could ensure that the process actually ran every day.  Mbizup's solution was to determine the last run date and select from there.  Technically that is safer than my solution since it is always possible that something could disrupt the running of the batch file.
0
Commented:
Thanks for that clarification, Pat.  I missed that in the question thread.
0
Commented:

PatHartman wrote:
>> the objective is to pick up items from the previous run day

If that's the case, then yes you should definitely consider handling missed days as the query I suggested doesn't handle that.

Correction: I misread the query. Using WHERE Completed_DT < Date() comes closer. However, I don't think it'll account for multiple missed days.   Whether or not that's an issue depends on the process.

Also, as mbizup mentioned it's important whether the column contains a date only or both a date and time.  However, if your column is indexed, be careful with functions.  I don't recall how Access works, but with most databases using a function on an indexed column, like WHERE INT(Completed_DT) ... prevents the database from using the index. A more index friendly construct for say - yesterday would be something like:

WHERE IndexedDateTimeColumn >= Date()-1   -- 2018-09-24 at midnight
AND      IndexedDateTimeColumn < Date()         -- 2018-09-25 at midnight

The same logic could be applied to any date range.
0
AnalystAuthor Commented:
the two queries i posted was my solution.

to elaborate, my company's daily production gets stored on a  sql server every night at midnight. I cannot access real time information, only what happened the day before. So every day, I am running a query to update a list with information from the day before. Today is Wednesday, Tuesday production was added to my list today. We never work Sundays but sometime we do work on Saturday.

I don't want to run query manually.  I have windows task scheduler  opening an Access database that runs a macro using Access's autoexec feature (runs upon opening). From Tues - Thurs, i need records from the day before.  For it to be truly automated, I have to deal with Monday. On Monday I need to append records dated that previous Friday or Saturday, if anyone worked on Saturday.

the following achieves this unless you can see otherwise:
FROM [table]....
WHERE
OR

Weekday(Date() on Monday is 2

the first line under the WHERE clause specifies  if today is Monday then, then date() - (1+Weekday(Date()) which is the same as  date()-3 . (last friday) , otherwise date()-1 (yesterday).
The line following OR captures Saturday
It reads if today is Monday then Date()-Weekday(Date()  which is the same as Date() - 2 ( Saturday ).
The rest of the IIF statement reads that if it is not Monday, then Date()--0.

date()-0 will never pull any data but the objective is achieved.
0

Experts Exchange Solution brought to you by