My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).

The Setup:
I'm running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(.  My database is compiled and
resides on a network drive. The database has not been split into a
front and backend, as I don't have the tools to effectively
redistribute the front end to the more than 350 (occasional) users
every time I make a change. Although the database currently holds only
650 records, it is expected to contain around 10,000 records after it
has been up and running for a few months. (I.E., my problem will get
worse, not better). The results of this query display in a read only
continuous forms format.

I've isolated 98% of the problem down to a counter routine I've called
in the query. This routine tallies and displays the number of days in a
date span minus weekends and company holidays. The purpose is to
display the total age of an 'incident' (one record), the length of time
it has been in its current department, and the length of time its
current owner has 'owned' an incident. With these counters disabled,
the query runs in 5 seconds, (which is perfectly acceptable). I need to
get this query to display all the numbers I need it to in no more than
10 seconds.

The routine itself I shamelessly borrowed from another article as a
workaround (regrettably, I don't remember his name) when I discovered
that the DateDiff function provided with A97 couldn't correctly compute
weekdays only. (Apparently the "w" argument in the DateDiff function of
A97 does not, has not, nor ever will function correctly)

The problematic code is pasted below. I've been looking for ways to
tweak it a bit, and managed to increase performance by 40% by moving
the SET db and RST statements to a separate public function that runs
only once rather than each and every time the loop runs. It is still
too slow though.

This code iterates one time for every day between two date spans and
increments a counter if the date it is currently evaluating is not a
weekend or company holiday.

Public Function srcWorkingDays2(StartDate As Date, EndDate As Date) As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database

Set db = CurrentDb
Set RST = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

intCount = 0

Do While StartDate < EndDate

RST.FindFirst "[HolidayDate] = #" & StartDate & "#"
If WeekDay(StartDate) <> vbSunday And WeekDay(StartDate) <> vbSaturday
Then
   If RST.NoMatch Then
       intCount = intCount + 1
   End If
End If

StartDate = StartDate + 1

Loop

srcWorkingDays2 = intCount
End Function

This is an example of one of the expressions in the query that calls
this function:
TOTDaysOpen:
srcworkingdays2(Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNull([dateclosed]),Date(),Format$([dateclosed],"mm/dd/yyyy")))

A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I've also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.

However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.

I haven't the foggiest idea how I might go about setting that up.

Any tips or suggestions any of you may have would be much appreciated.