Access 2002 - qry submit date older than 2 Business days

[kwagner1]

Hello,
In Access (2002), i'm using the following criteria (in "design view") to pull records that have a submit date older than 2 days.

Criteria: < Date () - 2

What i REALLY want to do is get records with a submit date older than 2 WORKDAYS (mon - fri). (i.e. "Business days")

How can i do this is Access?

thanks!!
Ken[/quote]

 

[TheToeOfPooh]

Ken

The following maybe what you need

In a Query
based upon relevant table
e.g. tblLoansAndReturns
Select your Field e.g. ReturnsDate
Criteria is <Now()
This asks for all records where the returns date is older than today
Add the other associated fileds as required.


In additon I have setup (with help from this board) a
default value
in the field ReturnsDate
definition within
tblLoansAndReturns
of
DateAdd("d",20,Now())
This looks at the system date and adds 20 days as the default for anticipated returns.

HTH
Bernard

 

[kwagner1]

not sure i understand..... (or maybe my request wasnt clear enough??)

My table has a field called "SubmitDate".... i want to query the table and pull records that have been submitted more than "2 Business Days" ago. The key here is that i'm looking for "business day" logic...

example:
if i run my query on a Sunday, i want to get records submitted more than 2 business days ago (so, i want to ignore records submitted on Sat, Fri, and Thur and show records Wed and prior.....)

hope thats clear..... thanks!

 

[TheToeOfPooh]

My apologies, the central bit of the criteria didn't appear and I failed to check my answer.

I would of used the same criteria as yourself
<Date()-2
so not much help there.

The only option which does not involve writting some code is a criteria
Like[]
When you run the Query this will present you with an Input box for the date.

Can anyone else help on this?
HTH
Bernard

 

[StevenSkinner]

working out Business Days in Access ???

Did anyone find the answer with regards to working out Business Days in Access ???

I Know the function in Excel is called "Workdays", but this function is not present in Access.

Any Ideas ???

 

[Andrew Fergus]

Hi Steve
I worked out how to calculate work days in this thread. You might want to experiment with the work days query first and then introduce it into your own query, with the appropriate criteria. It would be nice if there was an easier way.....
HTH, Andrew.