I was hoping there was some subquery I could run to look for the next highest
day and populate date2 with that. I'm not looking for software to be
telepathic. That would be ridiculous.

When I need a 1 day lag when I pull data just for one day I simply use the
same end date as before and then query that date -5 days (in case there is a
weekend and holiday in between dates). Then I use another query that
contains the following subquery to calculate the 2nd highest date...

SELECT Max(AS_OF_DATE) AS 2ndMax INTO 2ndDate_Tbl
FROM 2ndMaxRaw
WHERE (((2ndMaxRaw.AS_OF_DATE)(SELECT Max(AS_OF_DATE)
FROM InfoTbl)));

Then I use the resulting date to query in the table where I need a 1 day
lag. When it's just 1 day I can ignore the actual date since I know
everything queried is for the same date. The problem with using this method
on a date range is I need to relate the 2 dates properly. I was hoping to
use the date2 to join the tables. This way you can use Access to keep track
of your weekends and holidays indirectly. Make sense?


--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali


"John W. Vinson" wrote:

On Thu, 13 May 2010 13:30:01 -0700, Michelle
wrote:

I also don't want to have
to create a holiday table since it is one more thing that would have to be
maintained. (Meaning it would get forgotten.)

Sorry, Michell, if you want Access to keep track of the holidays that *your
business* observes, you will have to somehow tell Access what those holidays
are.

The telepathic interface won't be out until Access 2021 (same release as the
time machine interface,which is how I knew...) bg

--

John W. Vinson [MVP]
.