I am creating a database for a mailing with Canadian addresses. I have formated the Postal Code in the Table, Query, and Form, but when I create the mailing labels the format is lost.

The format I put in the Table, Query, and Form is >A0A\ 0A0

This way the letters are all capitalized and there is a space between the first three characters and the second three.

In order for the label to look proper, I have the City, Province, and Postal Code all as one control source, how do I now format the Postal Code with a space between the first three characters and the second three on a mailing label???

This is what I have:
=Trim([City]&", "&[Province]&" "&[Postal Code])

I Tried:
=Trim([City]&", "&[Province]&" "& StrConv([Postal Code],1))

Any more suggestions would be appreciated.

[This message has been edited by GMT (edited 05-09-2001).]

Alter the original expression that joins the fields together to....

[City] & ", " & [Province] & ", " & StrConv([Postal Code],1)

HTH

This is what I had to begin with that did not work:

=Trim([City]&", "&[Province]&" "&[Postal Code])

This is what I tried after reading first suggestion:

=Trim([City]&", "&[Province]&" "&StrConv([Postal Code],1))

It still did not have a space between the first three characters and the second three characters.

Anymore suggestions would be greatly appreciated. Thank You.


[This message has been edited by GMT (edited 05-09-2001).]

Hi GMT (Greenwich Mean Time??)

Is it Trim that is causing you a problem? Or perhaps your data for the postcode is actually stored without the space being stored (I am sure there's an option somewhere to do this).

Have you tried:

=(Trim([City]&", "&[Province]))&" "& StrConv([Postal Code],1)

or doing without Trim at all?

HTH

Rich Gorvin

Try...

=Trim([City]&", "&[Province]&" "& Format(StrConv([Postal Code],1),"A0A\ 0A0"))

HTH

I have tried the two new suggestions with and without the Trim and it still doesn't seem to be working. I have also tried various orders and ways to put the suggestions together and I am still not having any success.

I have had one thing do something different:

=([City]&", "&[Province]&" "&Format(StrConv([PostalCode],1)))

This produced two spaces between the Province and the Postal Code. I didn't want that, but it was a change.

Is there someting I can put in the Form, Table, or Query that would make the format in those carry over to the report made in Label Wizard?

Or is there something I can put in the Format or Input Mask Properties to help?

Thanks for the past responses. Any other suggestions would be appreciated again.

Formats can be frustrating as they are for display purposes only and never get carried over from one object to the next.

I would suggest that you use an input mask in the table and make sure that you STORE all literal characters.

Use this as an INPUT MASK

>A0A"\ "0A0;0;-<

HTH

Kevin M

Me Again. I still can't get this to work for me. More suggestions would be grand.

Thanks,

GMT

I'm still trying many things. Does anyone have any suggestions that have not been mentioned earlier or know what I have done wrong??
Thanks,
GMT

gmt,

revisit kevin's last post...

but use >A0A\ 0A0;0;-<

>A0A"\ "0A0;0;-< will cause the \ to be included in the postal code.


you may have missed his suggestion that this be placed in the

INPUT MASK

for the table. remove any entry you have for FORMAT in the table.

hth,

al
pugh consulting services

gmt,

did a quick test...looks like access is getting confused in the concatenation process.

since a valid postal code is always 3x3 characters, why don't you just use 'brute force' to get it into the proper format.

try something like:

=[City]&", "&[Province]&" "&LEFT$([Postal Code],3)&" "&RIGHT$([Postal Code],3)

hth,
al
pugh consulting services

GMT,

In either the Form or Label Input mask try;

>A0A 0A0

just leave a space between the 3rd and 4th characters.
It works for me on forms although the table remains without the space.

? you may not need > if that is in the table input mask

HTH

David



[This message has been edited by DH (edited 05-23-2001).]