previous day

[cager]

I have a query that is pulling from a table the previous days data using
date()-1.

I have addressed the problem if I'm running this query on a Monday then the
table's last days data would be Friday (the last buisness day) so date()-1
would not work so I came up with this.

IIf(Weekday(Date())=2,Date()-3,Date()-1)

However, I thought what if there is a holiday like a 3 day weekend where my
company has Monday off so I come in on Tuesday looking to pull the prevous
days data. Well the previous data would be Friday (not Monday since we had
the day off) so my iif statement would not work.

Any idea how I can get this query to just pull the last "business day". My
company does have a table that has a list of all dates and whether it's a
business day (designated by "B" in the field), holiday (designated by "H" in
the field), or weekend (designated by "W" in the field).

 

[Michael Gramelspacher]

I have a query that is pulling from a table the previous days data using
date()-1.

I have addressed the problem if I'm running this query on a Monday then the
table's last days data would be Friday (the last buisness day) so date()-1
would not work so I came up with this.

IIf(Weekday(Date())=2,Date()-3,Date()-1)

However, I thought what if there is a holiday like a 3 day weekend where my
company has Monday off so I come in on Tuesday looking to pull the prevous
days data. Well the previous data would be Friday (not Monday since we had
the day off) so my iif statement would not work.

Any idea how I can get this query to just pull the last "business day". My
company does have a table that has a list of all dates and whether it's a
business day (designated by "B" in the field), holiday (designated by "H" in
the field), or weekend (designated by "W" in the field).

Using the calendar table, possibly this,
but using your real table and column names
(not tested)

SELECT * FROM SomeTable AS s
WHERE s.Business_day =
(SELECT Max(c.calendar_date)
FROM Calendar AS c
WHERE c.calendar_date Between Date()-1 and Date()-7
AND c.calendar_date < Date()
AND DatePart("w",c.calendar_date) = 6
and c.work_day = "B"
and c.holiday <> "H");