SELECT IF in Access

yballan used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead
try use IIF function instead.

MS Access: IIF Function


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.
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
What about:
IIF(IsNull(Clients.[Address]), Clients.[Billing_Addr], Clients.[Address])

Open in new window

IIF(Clients.[Address]="", Clients.[Billing_Addr], Clients.[Address])

Open in new window

In Access you would use <>, rather than !=
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.


Thank you Experts for getting this right!!
NorieAnalyst Assistant

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

Nz(Clients.[Address], Clients.[Billing_Addr])
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial