Where string takes forever when using dates only

[TonyG]

Hello.

When i use the following string in a "DoCmd.OpenReport stDocName, acViewPreview, , strWhere", it gives me the report i want:
"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#)"

Yet if i use this string, it takes forever to get a report. Can somebody explain why ?

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

Here is the query that is the Record Source for the report.

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET, [PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION, INSTALL.TIMESTAMP
FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Any help would be greatly appreciated.

Thanks,
Tony

 

[Crowley16]

in the first query, you are limiting your results to only specific companies, and THEN you are looking at those companies with a IN subquery, this usually takes a long time.

in the 2nd query, you are looking with a IN for ALL your records...

--------------------
Procrastinate Now!

 

[TonyG]

Hello Crowley16.

Thanks for your reply.

I guess i was really looking for suggestions on how to improve the speed of the second, dates only, query.

I received a suggestion from someone to use the "EXISTS" clause instead of the "IN" clause, but i don't know how to do that.

Any help would be greatly appreciated.

Thanks,
Tony