| Author |
 Topic  |
|
|
sportsguy
Starting Member
USA
29 Posts |
 Posted - 02/04/2013 : 15:34:14
|
The Step by Step book by Mike Hotek shows how to create calculated columns, and that is fine.
However, can the calculation include an IIF statement?
I only want to calculate aver prices where there is a positive,
greater than zero volume.
thanks in advance.
sportsguy. . . .
MS Access 20 years, SQL hack |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 02/04/2013 : 15:45:08
|
Yes, IIF or CASE expressions can be used for either computed columns or in a SELECT list.
ie:
assuming a column called [volume] and assuming you want to return NULL if it is not greater than 0:
case when [volume] > 0 then [volume] else null end
EDIT:
or
IIF([volume] > 0, [volume], null)
Be One with the Optimizer
TG |
Edited by - TG on 02/04/2013 15:47:11 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/04/2013 : 22:51:32
|
are you asking about MS Access query of Transact SQL query?
If former you can use as TG suggested
If in T-SQL, IIF is available only from SQL 2012 onwards so if you're on an earlier version you should be using CASE...WHEN instead
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sportsguy
Starting Member
USA
29 Posts |
Posted - 02/05/2013 : 12:12:03
|
TG,
Got it, thanks, understand perfectly.
Next quick question. . . can i change an existing column from numeric to calculated?
or do I have to drop the column and add it back?
somehow i suspect the latter, but just wanted to ask - definately a noob question!
sportsguy
MS Access 20 years, SQL hack |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/05/2013 : 12:20:20
|
from Books online
ALTER COLUMN
Specifies that the named column is to be changed or altered.
The modified column cannot be any one of the following:
A column with a timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Tables with Calculated fields
