Microsoft question regarding IIf statement or a better solution

August 28, 2024

I am working on a report that needs international addresses that are correct to the country’s standard to where the mail is going.

Some countries want the postal code before the City, others don’t and some have regions/states, while others dont.

I have this expression:
=IIf(Eval([country] In ("Italy","Czech Republic","Argentina","Austria","Belgium","China")),([zip] & " " & [city]),([city] & ", " & [region] & " " & [zip]))

I might have another 5-10 countries to put in.. Is there a better way of handling this in expression builder with like a lookup or something? I did not know if there is a limit to how long a expression can be in Access.

Thanks for any input.

It is working right now, but looking for a better solution.

>Solution :

Consider creating a function. This function accepts Country, City, Region, and Zip as parameters. Within the function use a SELECT CASE statement on Country and return the appropriate formatted string for each case.

In a MODULE add

Public Function CityZip(strCountry As String, strCity As String, strZip As String, strRegion) As String

  Select Case strCountry
  Case "Italy", "Czech Republic", "Argentina", "Austria", "Belgium", "China"
     CityZip = strZip & " " & strCity
  Case Else
     CityZip = strCity & " " & strRegion & " " & strZip
  End Select

End Function