[AccessD] IIf with three fields

[Kostas Konstantinidis]

Jim, Steve and David,
All of your solutions work :-))

Many thank's
/kostas



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David McAfee
Sent: Wednesday, March 29, 2017 10:32 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] IIf with three fields

Try nz


Select Nz ([mobile],Nz ([phone_home],NZ ([phone_job],"No Phone") As PhoneNum
>From SomeTable


If you want all 3 together, try something like:

Select
IIF (Len (trim (nz ([mobile],"")))>0,"Mobile: " & trim ([Mobile]),"") & IIF
(Len (trim (nz ([mobile],"")))>0,"Home: " & trim ([phone_home]),"") & IIF
(Len (trim (nz ([mobile],"")))>0,"Job: " & trim ([phone_job]),"") From
sometable

On Mar 29, 2017 12:00 PM, "Kostas Konstantinidis" <kost36 at otenet.gr> wrote:

Hi all,
I am a little confused about how to make IIf to work in query with three
different phones fields while all or any of them may be null

I use

IIf([mobile] Is Null; [phone_home]; IIf([phone_home] Is Null; [phone_job];
IIf([phone_job] Is Null; [phone_home] & ", " & [mobile]; [phone_job] & ", "
& [phone_home] & ", " & ", " & [mobile])))

But it doesn't work in all cases

Any help please?
Thank's
/kostas



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com