How do I find the last business day of previous month in Access 20

[Guest]

I need to have the date of the last business day of the previous month in a
column in an Access 2000 query called ACTION DATE. I have a date column
named LST LIM CHG DT that is the date the credit limit was last changed but
it doesn't always have the last business day.

 

[Jeff Boyce]

Have you checked at mvps.org/access or using Google? There may already be
a routine written to do this.

Conceptually, it seems like you are looking for the last weekday of the
month previous to the current month. But that depends, how are you defining
"business day"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You are correct - I want the date of the last weekday of the previous month.

 

[Gary Walter]

PMFBI

If you don't care about holidays...

given any date field (say "DtFld"),
the last day of the previous month =

DateSerial(Year(DtFld),Month(DtFld),0)

if the WeekDay of that date is 1 (Sunday),
you would subtract 2 from the computed date
to get back to the last Friday of the month

if the WeekDay of that date is 7 (Saturday),
you would subtract 1 from the computed date
to get back to the last Friday of the month

else, you already have the last weekday in
your computed date

so...this "could" be done as computed fields
in a Jet query w/o using a user-defined function

SELECT
DtFld,
DateSerial(Year(DtFld),Month(DtFld),0) As LstDayPrevMnth,
SWITCH(WeekDay(LstDayPrevMnth)=1, LstDayPrevMnth-2,
WeekDay(LstDayPrevMnth)=7, LstDayPrevMnth-1,
True, LstDayPrevMnth) As LstWkDayPrevMnth
FROM
yurtable
WHERE
IsDate(DtFld)=-1

Jeff's advice may be better in the long run though
because in a function you can verify the initial
date (and what to do about bad data); plus,
you could add further code to handle holidays.
It may be a more sage path to follow being as
your subject does say "last business day,"
not "last week day."

 

[Jamie Collins]

Have you checked at mvps.org/access or using Google? There may already be
a routine written to do this.

Conceptually, it seems like you are looking for the last weekday of the
month previous to the current month. But that depends, how are you defining
"business day"?

Rather than a 'routine', I would recommend the OP use calendar table, a
standard SQL trick, with all the dates within the required range (a
decade, say) with a column to indicate whether it is or is not a
business day for the given enterprise.

This is related to the fallacy that you should never store something
that can be calculated e.g. the day of the week. When the rule is
'weekends are not business days' the calculation is simple. You only
need to add a further rule such as 'public holidays are not business
days' and the calculation is a bit costlier e.g. what are the public
holidays, moveable feasts, region-specific holidays, company-specific
events, etc. It could be that storing 'calculations' such as weekday,
week_number, is_business_day, first_day_this_month, etc for every day
in the decade is technically redundant but if it makes SQL queries
easier to write (and available to the enterprise beyond Access users
because it avoids embedded VBA) then it is certainly worth considering.

Jamie.

--