Mail merge

[Steve]

HI,

In my Access 2007 database I have a bunch of text fields that I would like
to merge into Publisher 2007. If for example I merge the "city" field I may
have a field size of 20 characters but the city could be something like
Boston which is 6 characters long. This leaves 14 blank spaces in the merge
document for city. I thought there was a way in Access to have it drop those
blank spaces if the entire field is not populated, thus allowing the fields
to come together? If so can someone help?

Thanks
Steve

 

[John Spencer]

Normally Access trims trailing spaces off of fields. There are some
situations where the trailing spaces may remain. Did you Import the data? Is
the table in SQL server and the field defined as Char and not VarChar? Is the
data in EXCEL or text file? Any of those could cause you to have trailing spaces.

You can trim off the spaces temporarily by using the RTrim or Trim function.

TRIM(City)

If the data is stored in one of the native (JET or ACE) databases, then you
can use an update query to delete the excess spaces permanently.

UPDATE YourTable
SET City = Trim([City])
WHERE City is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

Hi John

Let me see if I can answer your quesions.

I created the database using Access 2007 and the filed type was Text. I
didn't see any VarChar from the pull down menu

I'm linking this database to a Publisher file.

Now, where could I put the trim function if I wanted to use it. I use to
program in Access 98 but it's been a while and 2007 seems different. I'm not
sure where to be able to insert the Trim fuction to the field.


Thanks
Steve

Normally Access trims trailing spaces off of fields. There are some
situations where the trailing spaces may remain. Did you Import the data?
Is the table in SQL server and the field defined as Char and not VarChar?
Is the data in EXCEL or text file? Any of those could cause you to have
trailing spaces.

You can trim off the spaces temporarily by using the RTrim or Trim
function.

TRIM(City)

If the data is stored in one of the native (JET or ACE) databases, then
you can use an update query to delete the excess spaces permanently.

UPDATE YourTable
SET City = Trim([City])
WHERE City is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

HI,

In my Access 2007 database I have a bunch of text fields that I would
like to merge into Publisher 2007. If for example I merge the "city"
field I may have a field size of 20 characters but the city could be
something like Boston which is 6 characters long. This leaves 14 blank
spaces in the merge document for city. I thought there was a way in
Access to have it drop those blank spaces if the entire field is not
populated, thus allowing the fields to come together? If so can someone
help?

Thanks
Steve

 

[John Spencer]

From the sounds of things, I think I would try using an update query to strip
of the extra spaces.

In query design view
== Add your table
== Add the field to be updated
== Select Query: Update from the menu (hmm that's probably different in 2007)
== Enter the following in the UPDATE TO under the field
TRIM([TheFieldName])
== Enter the following in the criteria
Is Not Null
== Select Query: Run from the menu.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John

Let me see if I can answer your quesions.

I created the database using Access 2007 and the filed type was Text. I
didn't see any VarChar from the pull down menu

I'm linking this database to a Publisher file.

Now, where could I put the trim function if I wanted to use it. I use to
program in Access 98 but it's been a while and 2007 seems different. I'm not
sure where to be able to insert the Trim fuction to the field.


Thanks
Steve

Normally Access trims trailing spaces off of fields. There are some
situations where the trailing spaces may remain. Did you Import the data?
Is the table in SQL server and the field defined as Char and not VarChar?
Is the data in EXCEL or text file? Any of those could cause you to have
trailing spaces.

You can trim off the spaces temporarily by using the RTrim or Trim
function.

TRIM(City)

If the data is stored in one of the native (JET or ACE) databases, then
you can use an update query to delete the excess spaces permanently.

UPDATE YourTable
SET City = Trim([City])
WHERE City is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

HI,

In my Access 2007 database I have a bunch of text fields that I would
like to merge into Publisher 2007. If for example I merge the "city"
field I may have a field size of 20 characters but the city could be
something like Boston which is 6 characters long. This leaves 14 blank
spaces in the merge document for city. I thought there was a way in
Access to have it drop those blank spaces if the entire field is not
populated, thus allowing the fields to come together? If so can someone
help?

Thanks
Steve

 

[Steve]

HI John,

I finally figured out what went wrong. When I merged the database with
Publisher I had to add the additional fields within the text box. Fo
example, when I added the field, City, in Publisher it put a text box around
the field and inserted into my publication. In order to have State and Zip
code I simply double clicked on the remaining two fields and Publisher
inserted them with the City text box thus allowing for the trimming to take
affect. Initially I created three separate text boxes which stayed separated
by the length of the text box.

Hope this helps

Thanks
Steve

From the sounds of things, I think I would try using an update query to
strip of the extra spaces.

In query design view
== Add your table
== Add the field to be updated
== Select Query: Update from the menu (hmm that's probably different in
2007)
== Enter the following in the UPDATE TO under the field
TRIM([TheFieldName])
== Enter the following in the criteria
Is Not Null
== Select Query: Run from the menu.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John

Let me see if I can answer your quesions.

I created the database using Access 2007 and the filed type was Text. I
didn't see any VarChar from the pull down menu

I'm linking this database to a Publisher file.

Now, where could I put the trim function if I wanted to use it. I use to
program in Access 98 but it's been a while and 2007 seems different. I'm
not sure where to be able to insert the Trim fuction to the field.


Thanks
Steve

Normally Access trims trailing spaces off of fields. There are some
situations where the trailing spaces may remain. Did you Import the
data? Is the table in SQL server and the field defined as Char and not
VarChar? Is the data in EXCEL or text file? Any of those could cause
you to have trailing spaces.

You can trim off the spaces temporarily by using the RTrim or Trim
function.

TRIM(City)

If the data is stored in one of the native (JET or ACE) databases, then
you can use an update query to delete the excess spaces permanently.

UPDATE YourTable
SET City = Trim([City])
WHERE City is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
HI,

In my Access 2007 database I have a bunch of text fields that I would
like to merge into Publisher 2007. If for example I merge the "city"
field I may have a field size of 20 characters but the city could be
something like Boston which is 6 characters long. This leaves 14 blank
spaces in the merge document for city. I thought there was a way in
Access to have it drop those blank spaces if the entire field is not
populated, thus allowing the fields to come together? If so can someone
help?

Thanks
Steve