Finding previous business day.

[dodat via AccessMonster.com]

I have a list of each individual product but would like to find the previous
business day. I'm currently using
[order date]-1, returns the previous day. . . but i would like to find out
the previous business day. Is there a way to find the previous business day?
Thanks in advance.

 

[Guest]

your question is not clear.

what information of the prevous day do you seek? where is it recorded?

 

[dodat via AccessMonster.com]

Sorry to the confusion,

but i'm trying to find the previous day on a table of products that has a
specific date.

For example

Product Date Prior Business Day

Apple 10/23/2006 10/20/2006
Banana 10/16/2006 10/13/2006

How would I put the Prior Business Day based on today's date if the day falls

I have a list of each individual product but would like to find the previous
business day. I'm currently using
[order date]-1, returns the previous day. . . but i would like to find out
the previous business day. Is there a way to find the previous business day?
Thanks in advance.

 

[Guest]

ah ok - - there is a very large body of work involving dates and Access

part of the solution is also depending on your version as embedded features
directly involving date calculations have been added with each version - so
definitely check out your own Help in Access

There is probably an MVPer that has this solution at their fingertip and
will join in...otherwise you can search on this site and google around to
other places such as this:
http://www.mvps.org/access/datetime/date0012.htm


--
NTC

Sorry to the confusion,

but i'm trying to find the previous day on a table of products that has a
specific date.

For example

Product Date Prior Business Day

Apple 10/23/2006 10/20/2006
Banana 10/16/2006 10/13/2006

How would I put the Prior Business Day based on today's date if the day falls

I have a list of each individual product but would like to find the previous
business day. I'm currently using
[order date]-1, returns the previous day. . . but i would like to find out
the previous business day. Is there a way to find the previous business day?
Thanks in advance.

 

[Guest]

Do not use "Date" as the name of field - it is reserved word and naming a
field that way can cause all sorts of hard-to-trace errors. Let's assume
that your date field is named ThisDay.
In the Field row of the query grid, enter this, ALL ON ONE LINE:
PriorBusinessDay: DateAdd("w",
IIf(DatePart("w", Thisday) > 2, -1,
-(DatePart("w", ThisDay) + 1)), ThisDay)

What this does: DatePart returns the day of week - Sun=1, Mon=2 etc.
If this is >2 (Tue thru Sat), then subtract 1 day from ThisDate.
Otherwise, subtract daynumber + 1: 2 for Sun, 3 for Mon.
This has been tested as VBA function, but not in a query.