Calculated Fields in SQL

[Auntiejack56]

Hi all,
There was a time when this query would give an error, because you couldn't use a calculated field in a second calculation in the same query:
SELECT Len([EntrantName])*[EntrantID] AS EntrantCode, tblEntrants.EntrantName, IIf([EntrantCode]>50,[EntrantCode],[EntrantName]) AS EntrantDescription
FROM tblEntrants;
The SQL itself is just for illustration purposes (it works but has no real world application), but the point is that one calculated field, EntrantCode, is used in a second calculation for EntrantDescription.
My question is that I now have some quite complex calculations that I would like to use in a single query, but I'm hesitant to do so in case the results are subject to limitations that I may not be aware of. Can anyone advise?
Thanks,
Jack

 

[CJ_London]

no limitations in a standard select query in terms of returning columns (never has been as far as I know). There are limitations in group by queries because the alias (EntrantCode in your example) is not available.

However there are limitations on applying criteria or sorting which is what you may be thinking of.

In your example you can sort/apply criteria to the EntrantCode column, but not the EntrantDescription column.

 

[Auntiejack56]

Thanks. As I continue to work with ever newer versions of Access, I am slowly pulling out my old habitual workarounds that have accumulated since version 1.1 (some of which, as you suggest, may have resulted from misinterpretations anyway) ......