Filtering a query that a form is based on comes up with invalid field

[quickbiker]

Hi there, I have a form with a combo box that on after update opens a query and filters a field to show certain records that works fine. However, when I then run another query based on the filtered query I get invalid field on the field that is being filtered, hope that makes sense.

[JamesDeckert]

when I then run another query based on the filtered query I get invalid field on the field that is being filtered,

How are you accomplishing this? It would help if you copied the sql of both queries in your post.

[quickbiker]

Hi thanks for your response this is the sql for the qry that a form is based on as you can see the Depot field is filtered from an external form with a combobox. This works fin if I just use the tblmain, but when I introduce the other tables that's when I get the issue.

[quickbiker]

SELECT tblmain.Unit, tblmain.UnitName, tblmain.Unitid, tblmain.InspInt, qryUpdate6WweeklyInspections.MaxOfJobDate, Format([maxofjobdate],"ww") AS expr1, IIf([inspint]=[inspint],DateAdd("ww",[inspint],[maxofjobdate])) AS NextInsp, IIf(Nz([nextinsp]),Format([nextinsp],"ww")) AS weeknonextinsp, DateDiff("ww",[nextinsp],Date()) AS datedif, tblmain.Year, tblmain.ChassisNo, tblmain.Category, tblmain.Tracker, qry_tesdates_Crosstab.[2YRTACHO], qry_tesdates_Crosstab.[1YRTESTCERT], qry_tesdates_Crosstab.[6YRTACHO], qry_tesdates_Crosstab.HIAB, qry_tesdates_Crosstab.MOT, qry_tesdates_Crosstab.ROADTAX, qry_tesdates_Crosstab.LEZ, qryUpdate6WweeklyInspections.[Next Service], Query5.DriverName, Query5.MobileNo, tblmain.Depot, qry_lastdatetofield.Tofield, qry_lastdatetofield.TDate
FROM (((qryUpdate6WweeklyInspections RIGHT JOIN tblmain ON qryUpdate6WweeklyInspections.Unitid = tblmain.Unitid) LEFT JOIN Query5 ON tblmain.Unitid = Query5.Unitid) LEFT JOIN qry_tesdates_Crosstab ON tblmain.Unitid = qry_tesdates_Crosstab.UnitID) LEFT JOIN qry_lastdatetofield ON tblmain.Unitid = qry_lastdatetofield.UnitID
WHERE (((tblmain.Depot)=[forms]![frm_DepotSelection]![cboDepot]));

[JamesDeckert]

SELECT *
FROM tblmain
WHERE (((tblmain.Depot)=[forms]![frm_DepotSelection]![cboDepot]));

So you're saying that something like the above works, but when adding the other queries, that's when it breaks?
What is your error (or provide a screenshot).
It looks like you have several queries, does it break as soon as you put a single other query, or which query causes the problem?

[quickbiker]

Thanks for reply, I have to admit I haven't stripped the query down yeti had read that it could be the crosstab query, so I'll strip that out and see what happens, if that works can I then introduce the crosstab into another query using this as the source, I need this to work for the main form to function.

[quickbiker]

Just an update I have solved the issue I have totally gone in another direction completely, so now everything is working on my copy database just need to merg it into the main one after making a backup of course so thanks to all those that replied cheers.