>Would it speed up the query at all?

I just checked in VFP9 and it is just slightly faster to use the ANSI 92 SQL between instead of the BETWEEN() function.

100 runs of the same query returning only 1 record out of 100,000. 1.4598 for BETWEEN and 1.7455 for BETWEEN().

That's good. I like another reason to use ansi-92 sql. I feel it best to avoid mixing SQL and VFP syntax. It gets confusing when you go to straight SQL Server.

>
>
>>>Well folks, here's the query now. It's taking about 2-3 minutes to run, which I think
>>>is the best I'm gonna get.
>>
>>Actually the SQL syntax for between is
>>
>>"WeekOf BETWEEN {01/01/1990} AND {12/31/2004}"
>>
>>
>>>
>>>

>>>cWhere = "BETWEEN(WeekOf, {01/01/1990},{12/31/2004})"
>>>
>>>SELECT DocId, BoId, BoIdBase, RecType, OrdQty, Gross, StatName, ClientId, BInit,;
>>>       WeekOf, DStat_Net, DClnt_Net,DLComm, 0.00 AS NumCleared;
>>>  FROM LineItems;
>>>  WHERE &cWhere;
>>>  INTO TABLE LineTemp
>>>
>>>

>>>
>>>Notice BrokerId is no longer there. I will filter it out later.
>>>
>>>The index expression for Weekof is simply WeekOf, which is a date.
>>>
>>>Thanks!
>>>
>>>
>>>
>>>
>>>
>>>
>>>>

>>>>CREATE CURSOR TempBrok (BrokerID C(2))   -- or whatever the length is
>>>>INSERT INTO TempBrok VALUES ('MC')
>>>>INSERT INTO TempBrok VALUES ('')
>>>>
>>>>
>>>>
>>>>SELECT DocId, BoId, BoIdBase, RecType, OrdQty, Gross, StatName, ClientId, BInit,;
>>>>       WeekOf, DStat_Net, DClnt_Net,DLComm, 0.00 AS NumCleared;
>>>>  FROM LineItems;
>>>>     JOIN TempBrok ON TempBrok.BrokerID = LineItems.BrokerID
>>>>     WHERE  (your date clause)
>>>>  INTO TABLE LineTemp
>>>>

>>>>
>>>>However, I missed from your first post the # of rows you're bringing back...Mike Yearwood may be right, depending on how they're being brought down (across a LAN or whatever), that's gonna take some time.
>>>>
>>>>Kevin