Help with If Statement in Access Report

[jpfulton248]

Hi. Not great with Access but I managed to stumble through setting up a database with forms, reports etc. Need to make a change to a report that I currently use to create labels. This is a screen shot of what I currently have:

CHURCH_NAME -- The church name
CHURCH_2 -- the database has a separate field if the church name is too long for one line
Address -- physical address of street number and street name
=Trim([City] & ", " & [State] & " " & [Zip]) -- physical address of City State Zip



The database also includes fields for the mailing address. Some records have only a physical address, some have a physical and mailing address, and some only have a mailing address. I need the report to use the mailing address if it exist. For records with only a physical address and no mailing address, use the physical address.

The mailing address fields are:

MAIL_ADDR
MAIL_CITY
MAIL_STATE
MAIL_ZIP



Please help. Thank you in advance!

[ranman256]

No IF needed, Build a UNION query to pull the diffent addresses

qnAddrUnion =
Select name, MailAddr, MailCity, MailState, MailZip from table where [physical address] = null
union
Select name, PhysAddr, PhysCity, PhysState, PhysZip from table where [Mail address] = null

Then if needed you can run a query on qnAddrUnion to remove the duplicates:
select distinct * from qnAddrUnion