Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-17-2019, 02:59 AM   #1
Auntiejack56
Newly Registered User
 
Join Date: Aug 2017
Posts: 30
Thanks: 2
Thanked 3 Times in 3 Posts
Auntiejack56 is on a distinguished road
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

Auntiejack56 is offline   Reply With Quote
Old 09-17-2019, 03:10 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,333
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Auntiejack56 (09-17-2019)
Old 09-17-2019, 03:18 AM   #3
Auntiejack56
Newly Registered User
 
Join Date: Aug 2017
Posts: 30
Thanks: 2
Thanked 3 Times in 3 Posts
Auntiejack56 is on a distinguished road
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) ......

Auntiejack56 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated Fields Pauldohert General 8 06-22-2004 05:42 AM
help with calculated fields! angelfish Forms 0 06-15-2004 10:49 PM
calculated fields Webmaster@worksnotfun.com Forms 2 03-21-2002 08:46 AM
calculated fields WILSON Forms 2 04-26-2000 03:13 PM
Calculated Fields lnoles Reports 1 12-22-1999 11:22 PM




All times are GMT -8. The time now is 07:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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