Problem with address format

  • Thread starter Thread starter epete367
  • Start date Start date
E

epete367

I am using the following:
=([ShippingName]+Chr(13)+Chr(10)) & ([CompanyName]+Chr(13)+Chr(10)) &
([ShippingAddress1]+Chr(13)+Chr(10)) &
([ShippingAddress2]+Chr(13)+Chr(10)) & [ShippingCity] & "," & " " &
[ShippingState] & " " & " " &
IIf(Len([ShippingPostalCode])>6,[ShippingPostalCode],Left([ShippingPostalCode],5))

for a shipping address. The problem is if the fields are blank I still
end up with a comma (,) showing.
At times shipping address is blank if it is the same as mailing
address. How can I hide the unnecessary comma?
Thank you!
 
Continue using the + for concatenation vice the &

=([ShippingName]+Chr(13)+Chr(10)) & ([CompanyName]+Chr(13)+Chr(10)) &
([ShippingAddress1]+Chr(13)+Chr(10)) &
([ShippingAddress2]+Chr(13)+Chr(10)) & ([ShippingCity] + ", " ) &
([ShippingState] + " ") &
IIf(Len([ShippingPostalCode])>6,[ShippingPostalCode],Left([ShippingPostalCode],5))

I also don't understand the reason for the IIF statement. The way I read it
is says
If shipping Postal code is more than 6 characters then print the entire
shipping code
If shipping Postal code is 5 or less characters, then print all the shipping
postal code up to the 5 characters.
 
The reason for the IIf statement lies in the underlying form & table. I
used an input mask for the postal code. If I do not use the IIf
statement if the postal code has 5 digits it prints xxxxx- By using the
IIf statement if the postal code has 5 digits I now get xxxxx while if
there are 9 digits I get xxxxx-xxxx.
 
Back
Top