SELECT IF in Access

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

Do more with

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

MS Access: IIF Function
https://www.techonthenet.com/access/functions/advanced/iif.php
yballan

Author

Commented:
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
Commented:
What about:
IIF(IsNull(Clients.[Address]), Clients.[Billing_Addr], Clients.[Address])

Open in new window

Or
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.

Access the MQ here
yballan

Author

Commented:
Thank you Experts for getting this right!!
NorieAnalyst Assistant

Commented:
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

Commented:
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