[SOLVED] Microsoft question regarding IIf statement or a better solution

Microsoft question regarding IIf statement or a better solution

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(Optional strCountry As String="",Optional strCity As String="",Optional strZip As String="",Optional strRegion As String="") 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

You can then use the function as an expression instead of IIf:

=CityZip([country],[city],[zip],[region])