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

N

nandan

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.
 
J

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
 
G

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."
 
Top