I have developed a rather complex query using three separate subqueries (they aren't nested together. They each link to the main query). Because of its complexity, I developed it in a querydef until I got it working the way that I wanted it to. I then copied and pasted the SQL code into the form's RecordSource property through the Zoom box. Everything works great until I hit save. Then, all my controls get a warning saying that their control source is invalid. I try go to into design view on the form's Record Source and it says that it has an error in the FROM clause. I go back to the Zoom box and it has placed my subqueries in square brackets instead of parentheses. This seems to be the cause of the problem, but I can't get it to not change what I copy and paste into it. I was able to save my querydef just fine and it runs fine. I have now just changed the recordsource to the name of my querydef, but I have gotten into the habit of just having the raw code in my RecordSources so that I can see my RecordSource without a lot of looking.
Does anyone know why saving my form is changing my RecordSource and breaking it?
Here is the original query
and here is what it gets changed to
(Notice the square brackets on lines 4 through 8 and 11 through 15 surrounding the subqueries)
I just noticed that the square bracket starting line 4 ends on line 6 and then restarts to end on line 8. The next subquery does the same thing.
Does anyone know why saving my form is changing my RecordSource and breaking it?
Here is the original query
Code:
SELECT B.BillId_pk, B.CustId_fk, B.BillDate, B.Paid
, (SplitAmt*0.3)+LesAmt AS TotalBill, TotalPayments, ((SplitAmt*0.3)+LesAmt)-TotalPayments AS AmtDue
FROM ((tblBills AS B INNER JOIN
(SELECT BillId_fk, Sum(ReductionAmt) As SplitAmt
FROM tblBillDetails INNER JOIN tblLineItem
ON tblBillDetails.LineItemId_fk = tblLineItem.LineItemId_pk
WHERE LineItemTypeId_fk = 1
GROUP BY BillId_fk)
AS S
ON B.BillId_pk = S.BillId_fk) INNER JOIN
(SELECT BillId_fk, Sum(ReductionAmt) As LesAmt
FROM tblBillDetails INNER JOIN tblLineItem
ON tblBillDetails.LineItemId_fk = tblLineItem.LineItemId_pk
WHERE LineItemTypeId_fk = 2
GROUP BY BillId_fk)
AS L
ON B.BillId_pk = L.BillId_fk) INNER JOIN
(SELECT BillId_fk, Sum(PaymentAmt) AS TotalPayments
FROM tblPayments
GROUP BY BillId_fk)
AS P
ON B.BillId_pk = P.BillId_fk;
Code:
SELECT B.BillId_pk, B.CustId_fk, B.BillDate, B.Paid
, (SplitAmt*0.3)+LesAmt AS TotalBill, TotalPayments, ((SplitAmt*0.3)+LesAmt)-TotalPayments AS AmtDue
FROM ((tblBills AS B INNER JOIN
[SELECT BillId_fk, Sum(ReductionAmt) As SplitAmt
FROM tblBillDetails INNER JOIN tblLineItem
ON tblBillDetails].[LineItemId_fk = tblLineItem].[LineItemId_pk
WHERE LineItemTypeId_fk = 1
GROUP BY BillId_fk]
AS S
ON B.BillId_pk = S.BillId_fk) INNER JOIN
[SELECT BillId_fk, Sum(ReductionAmt) As LesAmt
FROM tblBillDetails INNER JOIN tblLineItem
ON tblBillDetails].[LineItemId_fk = tblLineItem].[LineItemId_pk
WHERE LineItemTypeId_fk = 2
GROUP BY BillId_fk]
AS L
ON B.BillId_pk = L.BillId_fk) INNER JOIN
(SELECT BillId_fk, Sum(PaymentAmt) AS TotalPayments
FROM tblPayments
GROUP BY BillId_fk)
AS P
ON B.BillId_pk = P.BillId_fk;
I just noticed that the square bracket starting line 4 ends on line 6 and then restarts to end on line 8. The next subquery does the same thing.
Comment