Need a favor: please paste this SQL to your db and saved it 1

[Clipper2000]

Hi everyone,

I have a query that is working perfectly except that some how my Access ALWAYS convert the ( into [ and add a period after the ] when I saved this query:

Thus, please do me a big favor by paste the query below to your Access query and save it, then open it again to see if Access has messed up the brackets. You don't need to have the tables setup, just paste the query as is will do.

Thanks in advance and thank you PHV for answer this question earlier.

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM [tbl90707(Single)] AS A INNER JOIN (SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B ON A.Accpac = B.Accpac
WHERE A.Accpac < "40000" AND A.Category <>"CFI" AND A.Period = 11 and A.Year = 2004;

 

[PHV]

The correct syntax is either:
INNER JOIN
(SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B
ON A.Accpac = B.Accpac
Or:
INNER JOIN
(SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) as B
ON A.Accpac = B.Accpac

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Clipper2000]

Hi Michael,

I have the database being discuss on my PC, and I can say that putting "" around the AS such as "AS" won't work. The syntaxes PHV proposed are correct one.

In all honestly, I learned this little "FROM (SELECT .....) AS alias" syntax from PHV in the last few days and for that I thank you.

 

[ByteMyzer]

The bug is in the query designer.

In Access97, inline-query syntax used the braces-dot method to enclose the inline-query. Example:
[tt]
SELECT * FROM [SELECT Field1, Field2 FROM Table1;]. AS TableAlias;
[/tt]

In Access2k and up, however, the inline-query syntax was modified from using the braces-dot enclosure to a simple pair of parenthesis, just like a subquery. Example:
[tt]
SELECT * FROM (SELECT Field1, Field2 FROM Table1;) AS TableAlias;
[/tt]

However, the query designer in the newer versions of Access continue to revert to the []. syntax whenever such a query/inline-query is modified, even though the newer versions of Access-SQL do not recognize this syntax. The query designer was not modified to accomodate this change in the syntax.

 

[Clipper2000]

Thanks for the info ByteMyzer. It appears that when the query is saved the first time, Access will writes to the system table with the correct syntax. Any subsequent runs will be based on the system table and the fact the query designer revert to the {}. syntax is ignored at that point.