error in qry design.... but it runs initaly?!?!

[jeffmoore]

This is the desired qry:

SELECT qry_distinct_data_3days.*, Results.StartDate
FROM Results INNER JOIN qry_distinct_data_3days ON (qry_distinct_data_3days.wall_min = Results.Material) AND (qry_distinct_data_3days.mill_num = Results.Line) AND (Results.[Heat Number] = qry_distinct_data_3days.heat_num) AND (Results.[Outer Diameter] = qry_distinct_data_3days.od_size)
WHERE (((Results.StartDate)>DateAdd("d",-4,Now())));

It will run fine for a while... but will return the following error after I close it and wait awhile. (Yes I have done a compact and repair)

Mill Quality Control can't represent the join expression qry_distinct_data_3days.mill_num = Results.line in Design view.
*One or more fields may have been deleted or renamed.
*The name of one or mor fields or tables specified in the join expression may be misspelled.
*The join may use an operator that isn't supported in Design view, such as > or <.

After the error the sql for the qry is:

SELECT qry_distinct_data_3days.*, Results.StartDate
FROM Results INNER JOIN qry_distinct_data_3days ON (Results.[Heat Number] = qry_distinct_data_3days.heat_num) AND (Results.[Outer Diameter] = qry_distinct_data_3days.od_size)
WHERE (((Results.StartDate)>DateAdd("d",-4,Now())));

The really wierd thing is the desired qry WILL run. I can save it and run it again, open it in design view ... etc.
BUT after awhile, say 5 minutes, it returns the stated error. The data types for the fields in question are compatable

TIA
Jeff in db hell

 

[JonFer]

Are any of the underlying queries crosstabs where the column headings could vary based on new data in the tables? When the error occurs, open up query
"qry_distinct_data_3days" and see if the field "mill_num" is really there. What is the SQL for this query?

You could try opening a blank database and importing the tables and queries and see if the problem persists.

Try copy/pasting the SQL for the problem queries into the SQL pane for a new query and save it over the old query.


John

http://www.interautomateconsulting.com