IIf function

[HLPCH]

I am trying to test the IIF statement and it does not work.

select iif(isnull(regions),'A','B') from customers

Could someone help me on this?

 

[mp9]

Try

SELECT IIf([regions] Is Null, 'A', 'B') AS RegionFlag
FROM customers;

http://www22.brinkster.com/accessory/

 

[straybullet]

Assuming A & B are field names in a table named Customers


select A, B from customers
where isnull(customers.region)

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

it does not work
Any error message ? Strange behaviour ? Unexpected result ?
Are we supposed to be mind reader ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HLPCH]

SELECT IIf([regions] Is Null, 'A', 'B') AS RegionFlag
FROM customers;

The above statement did not work. What i would like to see is that if Region field is null then I would like it to display 'A' else 'B'.

Thanks for the help

 

[PHV]

Are you sure that regions allows null ?
You may try this:
SELECT IIf(Trim([regions]) & ''='', 'A', 'B') AS RegionFlag
FROM customers;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HLPCH]

I get an incorrect syntax error . It does not like the "=" sign.I am using the customers table(Northwind database) to run this sql.

Thanks

 

[PHV]

Sorry for the typo.
SELECT IIf(Trim([regions] [tt]& '')='',[/tt] 'A', 'B') AS RegionFlag
FROM customers;
Variation:
SELECT IIf(Trim([regions] [tt]& "")="",[/tt] "A", "B") AS RegionFlag
FROM customers

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[straybullet]

Well thank goodness you knew what he meant PH lol

My apologies HLPCH, I must have read it wrong originally.

Let them hate - so long as they fear... Lucius Accius

 

[jjames]

HLPCH,

I think you were quite close with your original syntax...

select iif(isnull(regions),'A','B') AS RegionCode from customers