2007->2003 ; Parentheses->Brackets

[duluter]

Hi everyone.

I have a database I built in Access 2007 (using the older mdb file format). Everything works well. In a bunch of forms I have dropdown boxes that are populated using sql statements in their row source property, such as:

SELECT pID, Package FROM (SELECT [pID], IIf([IsCurrent]=No,'(OLD) ' & [PackageName],[PackageName]) AS Package, [IsCurrent] FROM tblPackage) AS x ORDER BY IsCurrent, Package;


When I open the file in Access 2003, for some reason Access 2003 replaces some of my parentheses above with brackets, like this:

SELECT pID, Package FROM [SELECT [pID], IIf([IsCurrent]=No,'(OLD) ' & [PackageName],[PackageName]) AS Package, [IsCurrent] FROM tblPackage] AS x ORDER BY IsCurrent, Package;


It then can't figure out the SQL statement because the syntax is incorrect.

Has anyone run into this problem before? It has me really stumped. Why would Access 2003 decide on its own to substitute brackets for parentheses?


Thanks,

Duluter

 

[datAdrenaline]

This is a behavior of the query compiler/optimizer for JET. The "square bracket" syntax is the sub-query syntax for Access 97 (A97 will not accept the parenthesis syntax).

So when you go from ...

SELECT * FROM (SELECT * FROM SomeTable) As vTbl

To this ...

SELECT * FROM [SELECT * FROM SomeTable]. As vTbl

There are a few circumstances where this becomes and issue ... an you have hit one, No nested square brackets are allowed! ...

But ... since you do not have any spaces or other special characters in your SQL statment, I would suggest that you modify the SQL statement in A2007 to look like this:

SELECT pID, Package
FROM [B][[/B]SELECT pID
             , IIf(IsCurrent=False,'(OLD) ' & [PackageName],[PackageName]) AS Package
             , IsCurrent FROM tblPackage[B]].[/B] AS x
ORDER BY IsCurrent, Package;

....

But with all this, why do you need the sub-query? ... I would think the following would work just fine and produce the same results, plus would be immune to any "helpful" square brackets being added ....

SELECT pID, IIf(IsCurrent=False,'(OLD) ' & PackageName,PackageName) AS Package, [IsCurrent]
FROM tblPackage
ORDER BY IsCurrent, PackageName;

....

For more information on the square bracket stuff, check out this recent thread ...
http://www.access-programmers.co.uk/forums/showthread.php?p=826916#post826916

Hope all this helps!

 

[duluter]

Thank you for the great response, Brent. I will give it a try.

"But with all this, why do you need the sub-query?"

Because I do everything the most convoluted way possible. There's something wrong with my brain.


Thanks again,

Duluter


EDIT: Wait, maybe there was a reason I used the subquery. I wanted to order by Package, not PackageName. Package is an alias, which can't be used in the Order By clause directly, right?

 

[datAdrenaline]

>> Because I do everything the most convoluted way possible. There's something wrong with my brain. <<

Welcome to the club!! ...

>> Wait, maybe there was a reason I used the subquery. I wanted to order by Package, not PackageName. Package is an alias, which can't be used in the Order By clause directly, right? <<

While it is true that you can not use an Alias in the ORDER BY clause, I don't see why you need to use the Alias due to the fact that your ORDER BY included the IsCurrent column ... and your expression for Package simply prepends text to PackageName based on IsCurrent ... so sorting by IsCurrent and Package is basically sorting by IsCurrent, IsCurrent, PackageName ... is it not? ... so sorting by IsCurrent, PackageName in the SQL I proposed should yeild the same result you were looking for .... or am I missing something?

 

[duluter]

Oh, yes, you are correct.

Thanks again for the help!


Duluter