Access changes formatting of a query and results in 'Jet engine cannot find...' error

[Margarita]

Hello,
This is my first post, please bear with me if it is too wordy.

I am working in Access 2003. I have a Vendors table and a PurchaseOrders table in a one to many relationship. The PO table holds, among other fields, the amount of each order, amount closed (paid off) so far (some orders are paid off in parts) and a requisition number that identifies the fiscal year in which the order was placed (in format 2012.00# where '00#' is the unique requisition number for fiscal year 2012, for example). What I am trying to do is get a sum per vendor of all fiscal year 2012 orders and the total of these that is closed so far, and the same for previous fiscal years' orders. I then want to use the resulting fields SpentFy12, ClosedFy12, Spent_previous, Closed_previous in a VendorProfile form which will give the user a quick snapshot of all the open encumbrances per vendor.

I am trying to accomplish the Spent and Closed sums in two separate queries:

SELECT 
V.VendorName, 
sum(P.POAmountActual) AS SpentFY12, 
sum(ClosedAmount) AS ClosedFY12
FROM 
(Vendors AS V) LEFT JOIN 
(SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*' ) AS P 
ON V.VendorName=P.VendorName
GROUP BY V.VendorName;

(the query for the previous fiscal years is identical except for 'not like '2012*''.

The problem is that this query works just great when I format it like that and run it. Then, as soon as I close it and go to create a form based on this query, I get a
'the recordsource you specified does not exist' error. I go back to check what's wrong with the query and I can't run it! I get the following error:

"the jet engine cannot find the source table or query
'[SELECT VendorName, POAmountActual, ClosedAmount FROM PurchaseOrders WHERE BHSRequisitionNum like '2012*'; ]...'

What keeps happening is that Access keeps putting a semicolon at the end of the select statement that is in the right position of the LEFT JOIN and after it does that, it can't run the query. Once I open it in SQL view and change the formatting to the way I had it, it runs perfectly again, but as soon as I save and close, I get the same problem all over again.

I tried repair the database, but still getting the same outcome. Can anyone please help with this tricky situation?
Thank you so much in advance!
-Margarita

 

[MarkK]

That's wierd.
See if it changes anything to structure the statement just a little differently ...

SELECT V.VendorName, 
sum(P.POAmountActual) AS SpentFY12, 
sum(ClosedAmount) AS ClosedFY12
FROM Vendors AS V LEFT JOIN PurchaseOrders As P
ON V.VendorName = P.VendorName
WHERE P.BHSRequisitionNum like '2012*'
GROUP BY V.VendorName;

See what I'm getting at there, removing the subquery and putting the where clause in the main query?

 

[Margarita]

Hello, I tried that before and it returned only the vendors which had an order entered in 2012- just like an inner join would. However, I did figure this out- and it turned out to be something ridiculously simple. I used IIF() to define the Spent and Closed fields and put in the BHSRequisitionNum criteria in there:

sum(iif(BHSRequisitionNum like '2012*', P.AmountActual, Null))

and then the left join without any criteria. That seems to be the simplest answer.

Thank you for the feedback!