Barbara,
When I need to do something along these lines, I add another table to
my data structure (tblNumbers) with one field (intValue). Then I put
10 records in it, one record for each value of zero to 9. I then
create a query (qryNumbers) to give me a source for larger numbers
(this one will give you the numbers 0 thru 999):
SELECT Hundreds.intValue * 100
+ Tens.intValue * 10
+ Ones.intValue as intNumber
FROM tblNumbers Hundreds, tblNumbers Tens, tblNumbers Ones
I'm assuming you have a RqnDate and OrderDate fields in you table. In
that case, you would write a SQL statement that looks something like:
SELECT *
FROM Orders O, qryNumbers N
WHERE O.OrderDate IS NULL
AND O.RqnDate <= (SELECT MIN(Expr1) as DateValue
FROM (SELECT TOP 7 Date()-[intNumber] AS Expr1
FROM qryNumbers
WHERE Weekday(Date()-[intNumber]) Between 2 And 6
AND Date() - intNumber NOT IN (SELECT dtHoliday FROM tblHolidays)
ORDER BY Date()-[intNumber] DESC))
This technique uses two subqueries to get your results. The first
subquery uses todays date and qryNumbers described above to get a set
of date values where the weekday function returns a value between 2
(Mon) and 6 (Fri) and where the date is also not in my tblHolidays
table). The TOP clause allows me to select the first 7 days from this
list that are not weekend or holiday dates. You can change the 7 to
whatever value you want.
The outer subquery selects the minimum date from among the 7 dates
previously selected. There is probably an easier way to do this, but
I've found that this technique works pretty well.
--
HTH
Dale Fye
I have used the following function: <=Date()-7
to generate a report that pulls purchase requisitions that were
received 7 or more days ago, but have not been ordered. I would like
this to only take business days into consideration. I need to skip
weekends and holidays. Can I add on to this expression so that only
business days are included?
This report is run daily and based off of the above function. Today's
date is not entered in each day.
