[AccessD] Query wierdness

The square brackets and period are actually the correct puntuation for
the virtual table you're creating with the subquery, it's just that the
Access 2000 query engine gets itself confused.  If you create the query
and don't reopen it, it should continue to work.  If you do have to open
it in SQL view, replace the square brackets and period with a pair of
parens and see if that quiets Access down.  They'll still be converted
behind the scenes, but it might save you having to rewrite the whole
query.

Charlotte Foust


-----Original Message-----
From: Joe Rojas [mailto:JRojas at tnco-inc.com] 
Sent: Monday, April 04, 2005 7:01 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Query wierdness


Hi All,
Access 2000 all SPs

I create a query that is as follows:

SELECT tblSOAttributes.ShopOrder, tblSOAttributes.OpNum, 
tblSOAttributes.AttributeNum, tblSOAttributes.Attribute, 
IIF(tblSOAttributes.Device2 Is Null, tblSOAttributes.Device1, 
tblSOAttributes.Device1 & " / " & tblSOAttributes.Device2) AS Device, 
T1.NumPart, T1.Measurement
FROM tblSOAttributes LEFT JOIN 
(SELECT * FROM tblMeasurements 
WHERE NumPart = [Forms]![In Process Data Collection]![txtNumPart]) AS T1

ON (tblSOAttributes.AttributeNum = T1.AttributeNum) AND 
(tblSOAttributes.OpNum = T1.OpNum) AND 
(tblSOAttributes.ShopOrder = T1.SONumber)
WHERE 
tblSOAttributes.ShopOrder = [Forms]![In Process Data
Collection]![txtSONum] 
AND tblSOAttributes.OpNum = [Forms]![In Process Data
Collection]![txtOpNum] 
AND ([Forms]![In Process Data Collection]![txtInProcess] = 1 OR 
tblSOAttributes.Inprocess = [Forms]![In Process Data
Collection]![txtInProcess])
ORDER BY tblSOAttributes.AttributeNum;

For the most part the query works great.
But when Access saves the query, it saves it as:

SELECT tblSOAttributes.ShopOrder, tblSOAttributes.OpNum, 
tblSOAttributes.AttributeNum, tblSOAttributes.Attribute, 
IIF(tblSOAttributes.Device2 Is Null, tblSOAttributes.Device1, 
tblSOAttributes.Device1 & " / " & tblSOAttributes.Device2) AS Device, 
T1.NumPart, T1.Measurement
FROM tblSOAttributes LEFT JOIN 
[SELECT * FROM tblMeasurements 
WHERE NumPart = [Forms]![In Process Data Collection]![txtNumPart]]. AS
T1 ON (tblSOAttributes.ShopOrder = T1.SONumber) AND 
(tblSOAttributes.OpNum = T1.OpNum) AND 
(tblSOAttributes.AttributeNum = T1.AttributeNum)
WHERE 
tblSOAttributes.ShopOrder = [Forms]![In Process Data
Collection]![txtSONum] 
AND tblSOAttributes.OpNum = [Forms]![In Process Data
Collection]![txtOpNum] 
AND ([Forms]![In Process Data Collection]![txtInProcess] = 1 OR 
tblSOAttributes.Inprocess = [Forms]![In Process Data
Collection]![txtInProcess])
ORDER BY tblSOAttributes.AttributeNum;

Notice that the parentheses around the sub query were replaced with
brackets and there is a period at the end of the sub query, before AS.
Is this normal? Normally I wouldn't care but I the query started yelling
at me about a syntax error near the from clause and I could even open
the query in design view to adjust any thing. It would show the query,
display the aforementioned error, I would click ok and it would close
the query design window. I had to delete the query and rewrite it!

JR 




This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please
notify the sender as soon as possible and destroy this message. While
TNCO, Inc. uses virus protection, the recipient should check this email
and any attachments for the presence of viruses. TNCO, Inc. accepts no
liability for any damage caused by any virus transmitted by this email.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com