>Hi Dave,
>
>I'm checking the thread at home so I won't be able to check the label refresh suggestion until tomorrow.
>
>I'd welcome any suggestions regarding optimizing the SQL statement

Hmmmm... lessee...

The original (for the studio audience) was:
>SELECT *
>FROM plc,lne;
>WHERE lne_placement_id = plc_placement_id ;
> AND lne_survey_id $ ThisForm.cSurveyString ;
> AND SUBSTR(lne_lane_number,1,1) $ 'RL' ;
> AND !lne_unable_to_rate ;
> AND !lne_unsafe_to_rate ;
>ORDER BY lne_placement_id, lne_lane_number;
>INTO TABLE data\pmi_inventory;
>DATABASE data\pmib;
>NAME pmi_inventory

My version:

lcSurveyString = ThisForm.cSurveyString
SELECT ;
---> * ;
---> FROM lne ;
-------> INNER JOIN plc ;
-------> WITH lne.lne_placement_id = plc.plc_placement_id ;
---> WHERE NOT lne.lne_unable_to_rate ;
-------> AND NOT lne.lne_unsafe_to_rate ;
-------> AND LEFT(lne.lne_lane_number,1) IN ("R", "L") ;
-------> AND lne.lne_survey_id $ lcSurveyString ;
---> ORDER BY lne_placement_id, ;
-------> lne_lane_number ;
---> INTO TABLE data\pmi_inventory ;
-------> DATABASE data\pmib ;
-------> NAME pmi_inventory

1) I'm sure that you'll buy something by using a local variable instead of property for SurveyString - if nothing else, access is faster, and it may well take care of your .Refresh() problem.

2) I always prefer the JOIN clause personally, being a bit of a SQL purist. But I just checked in Tamar's book, and she makes no comment about the speed impact that I saw.

3) I set up the order of the WHERE clauses in order of complexity, and if you want to index the tables to take advantage of Rushmore, this (hopefully) makes it clear what you need to consider indexing.

Let me know how this goes - obviously, I couldn't test this thing out on my own.