No Current Record error on Yes/No field

  • Thread starter Caroline Middlebrook
  • Start date
C

Caroline Middlebrook

Hi there,

I have a query which is pulling data from a couple of other queries
joined with a small table. I have added a new field to one of the
underlying tables and I need to include this field in the query. It is
a Yes/No field. For some reason, when I include it in the query, I get
a 'No Current Record' error. I have been able to include it in other
queries but just not this one.

Here is the SQL of the original query that WORKS:

SELECT Status_Summary_qry.Job_Number, Status_Summary_qry.Status_ID,
Status_Summary_qry.Can_be_Planned, Status_Summary_qry.Job_Type,
Status_Summary_qry.Description, Works_Location_tbl.Works_Location_ID,
Works_Location_tbl.Location,
Work_Planner_Detail_qry.Start_Date_For_Week,
Work_Planner_Detail_qry.Shift_Type,
Work_Planner_Detail_qry.Weekly_Plan_ID,
Work_Planner_Detail_qry.Start_Date_For_Week AS Mon_Date,
[Start_Date_For_Week]+1 AS Tue_Date, [Start_Date_For_Week]+2 AS
Wed_Date, [Start_Date_For_Week]+3 AS Thu_Date, [Start_Date_For_Week]+4
AS Fri_Date, [Start_Date_For_Week]+5 AS Sat_Date,
[Start_Date_For_Week]+6 AS Sun_Date
FROM (Status_Summary_qry LEFT JOIN Works_Location_tbl ON
Status_Summary_qry.Job_Number = Works_Location_tbl.Job_Number) LEFT
JOIN Work_Planner_Detail_qry ON (Works_Location_tbl.Works_Location_ID
= Work_Planner_Detail_qry.Works_Location_ID) AND
(Works_Location_tbl.Job_Number = Work_Planner_Detail_qry.Job_Number)
GROUP BY Status_Summary_qry.Job_Number, Status_Summary_qry.Status_ID,
Status_Summary_qry.Can_be_Planned, Status_Summary_qry.Job_Type,
Status_Summary_qry.Description, Works_Location_tbl.Works_Location_ID,
Works_Location_tbl.Location,
Work_Planner_Detail_qry.Start_Date_For_Week,
Work_Planner_Detail_qry.Shift_Type,
Work_Planner_Detail_qry.Weekly_Plan_ID
HAVING (((Status_Summary_qry.Status_ID)=1 Or
(Status_Summary_qry.Status_ID)=2 Or (Status_Summary_qry.Status_ID)=4)
AND ((Status_Summary_qry.Can_be_Planned)=-1));



and this is the new version with the extra field which is called
'Provisional':


SELECT Status_Summary_qry.Job_Number, Status_Summary_qry.Status_ID,
Status_Summary_qry.Can_be_Planned, Status_Summary_qry.Job_Type,
Status_Summary_qry.Description, Works_Location_tbl.Works_Location_ID,
Works_Location_tbl.Location,
Work_Planner_Detail_qry.Start_Date_For_Week,
Work_Planner_Detail_qry.Shift_Type,
Work_Planner_Detail_qry.Weekly_Plan_ID,
Work_Planner_Detail_qry.Start_Date_For_Week AS Mon_Date,
[Start_Date_For_Week]+1 AS Tue_Date, [Start_Date_For_Week]+2 AS
Wed_Date, [Start_Date_For_Week]+3 AS Thu_Date, [Start_Date_For_Week]+4
AS Fri_Date, [Start_Date_For_Week]+5 AS Sat_Date,
[Start_Date_For_Week]+6 AS Sun_Date,
Work_Planner_Detail_qry.Provisional
FROM (Status_Summary_qry LEFT JOIN Works_Location_tbl ON
Status_Summary_qry.Job_Number = Works_Location_tbl.Job_Number) LEFT
JOIN Work_Planner_Detail_qry ON (Works_Location_tbl.Works_Location_ID
= Work_Planner_Detail_qry.Works_Location_ID) AND
(Works_Location_tbl.Job_Number = Work_Planner_Detail_qry.Job_Number)
GROUP BY Status_Summary_qry.Job_Number, Status_Summary_qry.Status_ID,
Status_Summary_qry.Can_be_Planned, Status_Summary_qry.Job_Type,
Status_Summary_qry.Description, Works_Location_tbl.Works_Location_ID,
Works_Location_tbl.Location,
Work_Planner_Detail_qry.Start_Date_For_Week,
Work_Planner_Detail_qry.Shift_Type,
Work_Planner_Detail_qry.Weekly_Plan_ID,
Work_Planner_Detail_qry.Provisional
HAVING (((Status_Summary_qry.Status_ID)=1 Or
(Status_Summary_qry.Status_ID)=2 Or (Status_Summary_qry.Status_ID)=4)
AND ((Status_Summary_qry.Can_be_Planned)=-1));


Any help much appreciated as I'm quite stumped!!

Thanks,
Caroline
 
Top