Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Queries (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=8)
-   -   Calculated Fields in SQL (https://www.access-programmers.co.uk/forums/showthread.php?t=306866)

Auntiejack56 09-17-2019 02:59 AM

Calculated Fields in SQL
 
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 09-17-2019 03:10 AM

Re: Calculated Fields in SQL
 
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 09-17-2019 03:18 AM

Re: Calculated Fields in SQL
 
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) ......


All times are GMT -8. The time now is 07:31 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World