Problem with address format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

epete367@optonline.net - 31 Oct 2005 21:51 GMT

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!

Reply to this Message

John Spencer - 31 Oct 2005 22:14 GMT

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.

>I am using the following:
> =([ShippingName]+Chr(13)+Chr(10)) & ([CompanyName]+Chr(13)+Chr(10)) &
[quoted text clipped - 8 lines]
> address. How can I hide the unnecessary comma?
> Thank you!

Reply to this Message

epete367@optonline.net - 31 Oct 2005 22:23 GMT

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.

Reply to this Message