If...Then Statement Used in a Query

What I was needing was for a query to return its own field named txtaddress which would either show the BILLING address on file or the PHYSICAL address on file. If the contents of the PHYSICAL address were blank (made up of fields named add1, add2, city, state, zip), then it should assign txtaddress equal to the BILLING address (made up of filed names billing_address1, etc); otherwise, txtadresss should be equal to the PHYSICAL address fields.

The syntax, entered as a field-name in the query grid, looked like this:

address_line_1: IIf(IsNull([add1]),[bill_to_address1] & " " & [bill_to_address2],[add1] & " " & [add2] & " " & [add3])

address_line_2: IIf(IsNull([add1]),[bill_to_address3] & " " & [bill_to_address4],[city] & " " & [state] & " " & [zip] & " " & [zip2])

Again: if the contents of the field add1 (one of the fields which are part of the PHYSICAL address), then default to the fields of the BILLING address--eg., billing_address1, etc; otherwise, go ahead and use the fields of the PHYSICAL address (add1, add2, city, state, etc.)

A "cleaner" example of this would look kind of like this; assume that if the field name physical_address is null then the query's field named txtAddress would be assigned the value of the billing_address field; otherwise, it would assume the value of the physical_address field. The syntax entered in the field name information of the query would be:

"Abbreviate City Name" Example

If City value says "Tucson" then return "Tuc" else return "SV" (Sierra Vista)

=IIf([city]="Sierra Vista"),"SV","Tuc")

"Nested" Example (Abbreviates Sales Represenative/Customer Service):

=IIf(IsNull([video_sold_by]),"",IIf([video_sold_by]="Customer Service","Cust Svc","Sales Rep"))