Hi Everybody -
I have a quick question. I have a field in a query that gives me the
previous day using:
Issue Date: Date()-1
The issue date is always supposed to be the previous buisness day.
The formula that I have now gives me the previous calendar day. This
is no good for Monday, since I get Sunday's date.
I tried a couple of things with no success, like playing with Weekday
and anything else I could find in the builder that looked like it
could help. How can I get my field to populate the previous buisness
day's date? I was using Date() since it gave me the mm/dd/yyyy format
that I need.

Thanks for the help!

You could do something simple like:

Issue Date: IIf(weekday(#1/26/2010#,vbSaturday)>3, #1/26/2010#-1,
#1/26/2010# - weekday(#1/26/2010#,vbSaturday))

HOWEVER (big however) - how about Holidays? What happens when Christmas
happens on a weekday? How about when Christmas happens on a weekend but
celebrated (work-wise) or a Friday or Monday?

You might just want to set up a table of non-business days and the nunber of
days to subtract based on the date. Populate this table way into the future
and check it as part of the calculations.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Erick C" wrote:

> Hi Everybody -
> I have a quick question. I have a field in a query that gives me the
> previous day using:
> Issue Date: Date()-1
> The issue date is always supposed to be the previous buisness day.
> The formula that I have now gives me the previous calendar day. This
> is no good for Monday, since I get Sunday's date.
> I tried a couple of things with no success, like playing with Weekday
> and anything else I could find in the builder that looked like it
> could help. How can I get my field to populate the previous buisness
> day's date? I was using Date() since it gave me the mm/dd/yyyy format
> that I need.
>
> Thanks for the help!
> .
>

Erick -

This will check to see if today is Monday, and if so, subtract 3 days,
otherwise subtract one day. This will not work for holidays. If you want to
test for holidays, then you will need to build a table with your company
holidays and the date to use for those holidays...

Here is the simple one:

IIF(DatePart("w",Date())=2,Date()-3,Date()-1)

--
Daryl S


"Erick C" wrote:

> Hi Everybody -
> I have a quick question. I have a field in a query that gives me the
> previous day using:
> Issue Date: Date()-1
> The issue date is always supposed to be the previous buisness day.
> The formula that I have now gives me the previous calendar day. This
> is no good for Monday, since I get Sunday's date.
> I tried a couple of things with no success, like playing with Weekday
> and anything else I could find in the builder that looked like it
> could help. How can I get my field to populate the previous buisness
> day's date? I was using Date() since it gave me the mm/dd/yyyy format
> that I need.
>
> Thanks for the help!
> .
>