Avatar of yballan
yballan
Flag for United States of America asked on

SELECT IF in Access

Dear Experts,

I am trying to perform Select If in MSSQL  in Access, not having any success.   All I want to do is have 2 columns with addresses, pick the one that is not blank to use.

Name: Short text
Address: Short text
BillingAddress: Short text

The data will have either Address or BillingAddress filled, and I want to create mailing labels.

I tried to write SELECT IF ( Address != "", Address, BillingAddress) As LabelAddress From MyTable   and getting syntax error,  I am not sure if MSSQL support SELECT IF.
How could I accomplish this in Access?  Do I need to create VBA module?

Please advise.
Microsoft AccessVBA

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
yballan

ASKER
Dear Ryan, thank you for a quick response.
I am not able to get the syntax right.  I want to use either Address or Billing_Addr which ever is not blank.  What am I doing wrong?

SELECT  IIF (Clients.[Address] != "", Clients.[Address], Clients.[Billing_Addr])
FROM Clients;

Please advise.
ASKER CERTIFIED SOLUTION
Daniel Pineault

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
yballan

ASKER
Thank you Experts for getting this right!!
Norie

You might want to look at Nz for this rather than an Iif expression.


Nz(Clients.[Address], Clients.[Billing_Addr])
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

Another way to address this, if either of those could be zero length string "", would be:

IIF(Trim(Clients.Address & "") <> "", Clients.Address, Clients.Billing_Addr)