A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change addressing



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2004, 10:01 PM
Jim
external usenet poster
 
Posts: n/a
Default Change addressing

In our Data Base - automatically between the City and the Province there is a comma - in bulk mailing the Post Office wants us to NOT have the comma - is there a quick way to do that?

  #2  
Old April 29th, 2004, 06:34 AM
John Vinson
external usenet poster
 
Posts: n/a
Default Change addressing

On Wed, 28 Apr 2004 14:01:09 -0700, Jim
wrote:

In our Data Base - automatically between the City and the Province there is a comma - in bulk mailing the Post Office wants us to NOT have the comma - is there a quick way to do that?


A calculated field

BulkAddress: Replace([Address], ",", "")

to replace all the commas in the address field.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
  #3  
Old April 29th, 2004, 06:37 AM
John Nurick
external usenet poster
 
Posts: n/a
Default Change addressing

Hi Jim,

It depends how the data is stored. If the City and Province are stored
in one field like this
City, Province
you need to use an update query to remove the comma. If these are the
only things in the field and you're using an up-to-date installation of
Access 2000, or a later version, all you have to do is to put
Replace([XXX], ",", "")
in the Update To cell of the query and replace XXX with the name of your
field.

With earlier versions of Access the process isn't quite so simple.

If City and Province each have a field of their own, it means that
Access is inserting the comma as it formats addresses on the mailing
labels or envelopes. Typically this will be in a Report: find the
report, open it in Design view, find the comma (which may be in an
expression like
=[City] & ", " & [Province]
) and delete it.

and delete it.

On Wed, 28 Apr 2004 14:01:09 -0700, Jim
wrote:

In our Data Base - automatically between the City and the Province there is a comma - in bulk mailing the Post Office wants us to NOT have the comma - is there a quick way to do that?


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.