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.
JoeMommasMommaAnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

_agx_Commented:
Edit: Added psuedo example

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
aikimarkCommented:
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)

Open in new window


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)

Open in new window

where [vweekday] represents the Weekday([datecolumn]) value
0
Ryan ChongBusiness 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 ?
https://www.experts-exchange.com/questions/27891566/How-do-I-determine-the-previous-business-day-in-MS-Access.html
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
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
mbizupCommented:
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())

Open in new window

0
mbizupCommented:
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())

Open in new window

0
JoeMommasMommaAnalystAuthor 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:

WHERE ((([tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,1+Weekday(Date()),1))

which works well except it excludes Saturday.  it is rare but tasks do get completed on Saturday.
0
PatHartmanCommented:
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
mbizupCommented:
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())

Open in new window

0
JoeMommasMommaAnalystAuthor Commented:
Thanks for your help.
 I used WHERE ((([All Tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,1+Weekday(Date()),1))) OR ((([All Tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,Weekday(Date()),0)));
0
JoeMommasMommaAnalystAuthor Commented:
WHERE ((([All Tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,1+Weekday(Date()),1))) OR ((([All Tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,Weekday(Date()),0)));
0
PatHartmanCommented:
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
aikimarkCommented:
Using the Choose() function in your Where clause:
WHERE ([All Tasks].[COMPLETED_DT] = 
  Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1) )
OR ([All Tasks].[COMPLETED_DT] = 
  Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1) );

Open in new window

0
PatHartmanCommented:
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
aikimarkCommented:
Thanks for that clarification, Pat.  I missed that in the question thread.
0
_agx_Commented:
Correction - Misread query:

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
JoeMommasMommaAnalystAuthor 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  
((([All Tasks].[COMPLETED_DATE])=Date()-IIf(Weekday(Date())<3,1+Weekday(Date()),1)))
OR
((([All Tasks].[COMPLETED_DT])=Date()-IIf(Weekday(Date())<3,Weekday(Date()),0)));

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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
The safest solution is the one I suggested.  Run the macro EVERY day to pick up the previous day.  If there is nothing, then nothing gets appended.  If there is something, then something gets appended.  What if there is an emergency and the shop works ONE Sunday?  Why make a constraint that you don't need?  Why force yourself to have to solve that problem?  This is called defensive programming.  If you can be flexible, then be flexible.  Don't write code that you KNOW will break under a certain condition even if the boss says it will NEVER happen.  If you fall for that line, I've got a bridge to sell you.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.