Word2k: Formatting Access data with numbers and letters using MergeField and switches

[Neil Marsh]

(I'm using Word 2000.)

I've seen a number of posts here regarding problems people are having
with getting data from an Access database into a Word document using
MERGEFIELD and switches. For example, using { MERGEFIELD TEL \# "(###)
###'-'####" } to format the plain string of 10 digits that comes from
Access -- 1234567890 -- into a formal telephone number -- (123)
456-7890.

However, my project involves a series of ID codes that contain both
numbers *and* letters. Here are the three different sets of codes:

Field
Name Format Sample Codes
----- -------- -------------
CBCID #LL-## 3NF-12
DHID LLL-#### DHP-1234
ISBN #-#####-###-C 1-23456-789-X or
1-23456-789-1

# = any number L = any letter C = any number *or*
letter

I don't see any switches listed in Word Help that are for letters,
just ones for numbers. How can I format these properly?

(I'd rather do it this way and not have to mess with doing it via a
bunch of Queries in Access -- I've not had any luck understanding the
instructions some people have given on how to do that. :/ )

Thanks in advance for any help!

-/\/

 

[Doug Robbins - Word MVP]

Hi Neil,

If the field types in the Access table are set to Text (which they would
have to be to accept such data), you should not have any problem merging to
Word. There should be not formatting switches required.

What result are you getting when you execute the merge?

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Peter Jamieson]

I don't see any switches listed in Word Help that are for letters,

just ones for numbers. How can I format these properly?

Unfortunately, as you have discovered there are no switches that will help.
Which either means you use VBA in Word or...

(I'd rather do it this way and not have to mess with doing it via a
bunch of Queries in Access -- I've not had any luck understanding the
instructions some people have given on how to do that. :/ )

....you do it via a bunch of queries.

OK, so having to create queries just for this purpose is a pain, but it's
probably less of a pain than many of the other possible approaches. I
suspect the problem is that some people (including me) tend to be more
comfortable with writing queries directly in SQL, whereas others are more
comfortable with the tabular Access Query design pane. You also have to be
able to work out what character string functions you need - even the query
design pane isn't going to help you there.

If I'm creating a query in Access based on an existing table, what I
generally do is
a. work out what Access is actually storing and what combination of
functions I need to extract/transform the data
b. create a new query using the query design pane, including whatever
tables I need. This saves me having to get the various SQL Joins and
Jet-specific SQL syntax right
c. right-click in the query design pane title-bar (or top area) and select
SQL view. I then get to work directly with the SQL
d. toggle between SQL view, design view, and datasheet (results) view as
required. I use the right-click approach but maybe Access people know useful
keyboard shortcuts.

As far as the SQL is concerned,
a. you can use most of the VBA character, numeric and date manipulation
functions in expressions. Some may cause problems such as replace. You can
even use "user-defined" functions (i.e. written in Access VBA) but then you
have to connect to your data source from Word using DDE.
b. you can, and should, give your new columns names using AS `columnname`

e.g. The query Access (2000) sets up here when I create a new query based on
my xyz table is

SELECT *
FROM xyz
WITH OWNERACCESS OPTION;

Ignoring the WITH OWNERACCESS OPTION for the moment, if I want for example
to extract the first three characters of column abc, all I need do is modify
the query to

SELECT left(abc,3) AS `abc3`, *
FROM xyz

In the examples you give, you might find the following functions do the
trick, assuming the underlying fields are always actually text fields and
the formats are always precisely as you describe here:

CBCID #LL-## 3NF-12

left(cbcid,3) + '-' + mid(cbcid,4) AS `mycbcid`

DHID LLL-#### DHP-1234

left(dhid,3) + '-' + mid(dhid,4) AS `mydhid`

ISBN #-#####-###-C 1-23456-789-X or
1-23456-789-1

left(isbn,1) + '-' + mid(isbn,2,5) + '-' + mid(isbn,7,3) + '-' + mid(isbn,9)
AS `myisbn`

In fact you should be able to name the new fields `cbcid`, `dhid` and `isbn`
but I generally prefer to leave the original data as is.

Does that help? If not, can you indicate where you're getting stuck? Or is
it basically that you don't want to create Access queries to do this?

 

[Neil Marsh]

Hi Neil,

Hi, Doug. Thank you for responding.

If the field types in the Access table are set to Text (which they would
have to be to accept such data), you should not have any problem merging to
Word. There should be not formatting switches required.
What result are you getting when you execute the merge?

The punctuation wasn't showing up. I was just getting the codes without the dashes (-) in them.

However, I went back and checked it over again and discovered that the ISBN codes were coming through correctly. That led me to figure out what I had done wrong:

Here's a modified version of the original table, so other people can see what I was trying to do and what I did wrong (sorry about the formatting, not every newsread handles tabs the same way):

Field
Name Raw Data Access Mask Desired Result Actual Result After Merge
----- ------------- -------------------- ---------------- -----------------------------
CBCID 3NF12 !0CC-00;0;_ 3NF-12 3NF12 (same as Raw Data)
DHID DHP1234 !CCC-0000;0;_ DHP-1234 DHP1234 (same as Raw Data)
ISBN 123456789X or !0\-00000\-000\-C;0;_ 1-23456-789-X or 1-23456-789-X (this is correct)
1234567891 1-23456-789-1 1-23456-789-1 (this is correct)

( 0 = any number C = any character )

As you can see, in the Access Mask column, I had entered the masks for the first two fields WITHOUT the backslash ( \ ) preceding the dash ( - ), but did enter it for the third mask. The backslash tells Access to print the following character as part of the data. Without it, the dash gets dropped when the field is merged into Word.

I'd thought of this before, but assumed I was wrong when the errors continued to show up in the Word document. The trick was to *close* both the Word merge file *and* the Access file, then reopen them, otherwise Access wouldn't relay the change to Word.

It all works fine now. Thanks for the nudge in the right direction, Doug!

-/\/


---
Neil Marsh * (e-mail address removed)
Cambridge, MA * http://AudioBoy.net

****************************************************************
"In the dream you are falling, lost in the listening distance as
dark locks in ... Nightfall!"

-- Opening narrative to "Nightfall"
CBC Radio horror anthology, 1980-1983
****************************************************************