query for [datefld] within the past 5 workdays

[Karen]

I need to do a query to display records where the qcdate field is within the
past 5 work days. Does anyone have some ideas for me? I stuck good on this
one

Thanks in advance,

Karen

 

[Guest]

Hi

Try entering

=Date()-5 And <=Date(

into the criteria of the date field

HT

rp

----- Karen wrote: ----

I need to do a query to display records where the qcdate field is within th
past 5 work days. Does anyone have some ideas for me? I stuck good on thi
on

Thanks in advance

Kare

 

[Randy Harris]

Hi,

Try entering:


into the criteria of the date field.

If, by "work days", Karen meant to skip weekends and holidays, it will be a
bit more complex than that.

 

[Guest]

Hi Karen

As Randy so astutely pointed out, my suggestion does not allow for skipping weekends and holidays. I was able to find a post by Doug Steele, Microsoft Access MVP - taken from that post

Take a look at the Date/Time section of "The Access Web
http://www.mvps.org/access

There are a couple of solutions there. Both solutions use VBA code, one is short and the other is very long but it allows for various holidays

On the other hand, if VBA is beyond your current skill set (not trying to offend, but you did post in "New Users"), you might modify my original solution to

=Date()-7 And <=Date(

If your business is closed during weekends and holidays, then there should be no activity on the data during weekends and holidays. If that is the case, then this simplified solution will capture 5 work days worth of activity. This is a technically inaccurate solution, but realistically if you take any consecutive 7-day period, there will always be a Saturday and Sunday stuck in there somewhere during which there is no data activity. The "problem" with this solution is that if you have an extended holiday weekend (3 or 4 days?) then you'll end up with fewer than 5 working days worth of data

Sorry for not getting it right the first time and hope that this helps you find a solution that works for you

rp

----- Randy Harris wrote: ----

Hi

If, by "work days", Karen meant to skip weekends and holidays, it will be
bit more complex than that

 

[Karen]

I settled on using >=Date()-6, just because i didn't want to mess with VBA
coding for this; I wanted to keep it simple, a form and a query. We'll just
have to get over it when we have a three day weekend.

Thank you for your help.

Hi Karen,

As Randy so astutely pointed out, my suggestion does not allow for

skipping weekends and holidays. I was able to find a post by Doug Steele,
Microsoft Access MVP - taken from that post:

Take a look at the Date/Time section of "The Access Web"
http://www.mvps.org/access/

There are a couple of solutions there. Both solutions use VBA code, one

is short and the other is very long but it allows for various holidays.

If your business is closed during weekends and holidays, then there should

be no activity on the data during weekends and holidays. If that is the
case, then this simplified solution will capture 5 work days worth of
activity. This is a technically inaccurate solution, but realistically if
you take any consecutive 7-day period, there will always be a Saturday and
Sunday stuck in there somewhere during which there is no data activity. The
"problem" with this solution is that if you have an extended holiday weekend
(3 or 4 days?) then you'll end up with fewer than 5 working days worth of
data.

Sorry for not getting it right the first time and hope that this helps you

find a solution that works for you.

 

[Guest]

Glad you found something that worked for you

rp

----- Karen wrote: ----

I settled on using >=Date()-6, just because i didn't want to mess with VB
coding for this; I wanted to keep it simple, a form and a query. We'll jus
have to get over it when we have a three day weekend

Thank you for your help

Hi Karen

skipping weekends and holidays. I was able to find a post by Doug Steele
Microsoft Access MVP - taken from that postoffend, but you did post in "New Users"), you might modify my origina
solution tobe no activity on the data during weekends and holidays. If that is th
case, then this simplified solution will capture 5 work days worth o
activity. This is a technically inaccurate solution, but realistically i
you take any consecutive 7-day period, there will always be a Saturday an
Sunday stuck in there somewhere during which there is no data activity. Th
"problem" with this solution is that if you have an extended holiday weeken
(3 or 4 days?) then you'll end up with fewer than 5 working days worth o
data