Calculated Fields in SQL (1 Viewer)

Auntiejack56

Registered User.
Local time
, 02:05
Joined
Aug 7, 2017
Messages
55
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

Super Moderator
Staff member
Local time
Today, 17:05
Joined
Feb 19, 2013
Messages
12,471
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

Registered User.
Local time
, 02:05
Joined
Aug 7, 2017
Messages
55
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) ......
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom