formatting numeric data from Access merged into a Word table

L

LacieMoon

Greetings! I have created a very simple merge from a query in Access that
merges into a table in Word. The problem is a field defined as currency in
Access does not "carry over" the formatting into the Word document. I can put
a dollar sign in front of the field $<<Amount>> but I am dealing with large
numbers and need the comma separators. Does anyone know how I can carry over
the currency formatting from Access, OR correctly format the data field in
the Word table?
Thanks for any suggestions, Lacie
 
D

Doug Robbins

See "Formatting Word fields with switches" on fellow MVP Graham Mayor's
website at

http://www.gmayor.com/formatting_word_fields.htm


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
 
L

LacieMoon

Perfect Doug! Worked like a charm; thank you for directing me to a great
resource!
Lacie
 
S

Shannon

So, did I just take off the "100*" and format all 90 fields to be percentages
with 0 decimals in Access for naught? Are you saying I'm going to have to
re-do all that in Word? I had to do that for each field, because I when I
selected more than one field in the query, it took away the format options.
It took a long time.

Now, will I have to make these switch changes in the table in Word
individually? I've never used one, but this nonsense makes me want to switch
to a mac. Please tell me how to cluster or select all the inserted fields I
want and "switdh" them all in one command.

Shannon
 
M

macropod

For the currency, all you need to do is to add a picture switch to the
mergefield in you mailmerge template. To do this:
.. select the mergefield
.. press Shift-F9 to expose the field code. It should look something like
'{MERGEFIELD MyData}', where 'MyData' is your data field's name
.. delete everything between 'MyData' and the closing field brace
.. add ' \# $,0.00' after 'MyData', so that you end up with '{MERGEFIELD
MyData \# $,0.00}'
.. press F9 to update the field
.. run your mailmerge.

However, if you've already converted the values to percentages in Access
(heaven knows why), you can recover this in Word with just a little bit more
work. In addition to the above, before updating the field:
.. select the 'MERGEFIELD MyData' string in your mergefield
.. press Ctrl-F9 to insert a new pair of field braces, so that you get
'{{MERGEFIELD MyData} \# $,0.00}'
.. insert an '=' sign between the first two field braces and '*100' after the
third field brace, so that you get '{={MERGEFIELD MyData}*100 \# $,0.00}'

Cheers

--
macropod
[MVP - Microsoft Word]
PS: If you want $ without cents, make the picture switch '\# $,0'
PPS: Switching to a Mac would make no difference - the same situation
applies there too.
 
G

Graham Mayor

Word merge imports the raw data, so formatting it doesn't help much. See
http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
With a night's sleep, I can quit making mac threats to myself.

"Heaven knows why" tells me there's a cleaner way to do this whole
thing. Please let me itterate what you said, since this document has
not been published yet. The cleaner it is, the easier it will be for
someone else to understand when the church uses it in future surveys
(that's what this query is; percentages of survey results;no
currency).

Are you saying that Field Properties is not the best way to prepare
query values for merge fields? Would the cleanest way be to take the
raw ".23984879459403" into the merge document, then use switches to
make it pretty?

These values are merged into a table created in Word. Is there a way
to select and format the switches for all of these values in the
table (90 of them) with one command?

If not, does Excel bring over Field Properties from Access? If so,
would it be possible to get the data from Access to Excel, then
insert the Excel table into the Word document with Special Paste?
Would that be very unclean and cumbersome?

Thanks in advance (again).
macropod said:
For the currency, all you need to do is to add a picture switch to
the mergefield in you mailmerge template. To do this:
.. select the mergefield
.. press Shift-F9 to expose the field code. It should look something
like '{MERGEFIELD MyData}', where 'MyData' is your data field's name
.. delete everything between 'MyData' and the closing field brace
.. add ' \# $,0.00' after 'MyData', so that you end up with
'{MERGEFIELD MyData \# $,0.00}'
.. press F9 to update the field
.. run your mailmerge.

However, if you've already converted the values to percentages in
Access (heaven knows why), you can recover this in Word with just a
little bit more work. In addition to the above, before updating the
field: .. select the 'MERGEFIELD MyData' string in your mergefield
.. press Ctrl-F9 to insert a new pair of field braces, so that you
get '{{MERGEFIELD MyData} \# $,0.00}'
.. insert an '=' sign between the first two field braces and '*100'
after the third field brace, so that you get '{={MERGEFIELD
MyData}*100 \# $,0.00}'

Cheers

--
macropod
[MVP - Microsoft Word]
PS: If you want $ without cents, make the picture switch '\# $,0'
PPS: Switching to a Mac would make no difference - the same situation
applies there too.


Shannon said:
So, did I just take off the "100*" and format all 90 fields to be
percentages with 0 decimals in Access for naught? Are you saying
I'm going to have to re-do all that in Word? I had to do that for
each field, because I when I selected more than one field in the
query, it took away the format options. It took a long time.

Now, will I have to make these switch changes in the table in Word
individually? I've never used one, but this nonsense makes me want
to switch to a mac. Please tell me how to cluster or select all
the inserted fields I want and "switdh" them all in one command.

Shannon

:

See "Formatting Word fields with switches" on fellow MVP Graham
Mayor's website at

http://www.gmayor.com/formatting_word_fields.htm


--
Please respond to the Newsgroup for the benefit of others who may
be interested. Questions sent directly to me will only be
answered on a paid consulting basis.

Hope this helps,
Doug Robbins - Word MVP
Greetings! I have created a very simple merge from a query in
Access that merges into a table in Word. The problem is a field
defined as currency in Access does not "carry over" the
formatting into the Word document. I can put
a dollar sign in front of the field $<<Amount>> but I am dealing
with large
numbers and need the comma separators. Does anyone know how I can
carry over
the currency formatting from Access, OR correctly format the data
field in the Word table?
Thanks for any suggestions, Lacie
 
P

Peter Jamieson

Just a suggestion...
The cleaner it is, the easier it will be for someone else to
understand when the church uses it in future surveys (that's what this
query
is; percentages of survey results;no currency).

Every time you connect to Access data from Word there is some translation,
which implies that you may need to reformat the data the way you want in
Word. If you are inserting the data using mailmerge and { MERGEFIELD }
fields then you have the options to format them that have been suggested by
others. If you are inserting the data using Insert|Database then you have
less control over the format of each column.

Personally in trying to achieve a "clean" approach I would typically try to
opt for a "repeatable" approach and that would probably rely on a
combination of using the right data types in Access and applying the right
switches to MERGEFIELD fields in Word, or using a query to return /exactly/
the data I needed (e.g. if it should be a percentage value with a percent
sign at the end, return that as a string value and Word should not
re-interpret it as a number).

However, another approach that may work in your siuation if you do not have
need more columns than the maximum no. in a Word table is
a. write a query in Access so that the query returns the data you need, in
the format you need
b. select the query results in Access and Edit|Copy
c. Edit|Paste into a blank Word document
d. remove the top row
e. use that as the data source for a merge (or, if appropriate, insert the
table directly into the document you're trying to produce, then format it
using a simple macro).

Just my 2-c worth.

Peter Jamieson
Shannon said:
With a night's sleep, I can quit making mac threats to myself.

"Heaven knows why" tells me there's a cleaner way to do this whole thing.
Please let me itterate what you said, since this document has not been
published yet. The cleaner it is, the easier it will be for someone else
to
understand when the church uses it in future surveys (that's what this
query
is; percentages of survey results;no currency).

Are you saying that Field Properties is not the best way to prepare query
values for merge fields? Would the cleanest way be to take the raw
".23984879459403" into the merge document, then use switches to make it
pretty?

These values are merged into a table created in Word. Is there a way to
select and format the switches for all of these values in the table (90 of
them) with one command?

If not, does Excel bring over Field Properties from Access? If so, would
it
be possible to get the data from Access to Excel, then insert the Excel
table
into the Word document with Special Paste? Would that be very unclean and
cumbersome?

Thanks in advance (again).
macropod said:
For the currency, all you need to do is to add a picture switch to the
mergefield in you mailmerge template. To do this:
.. select the mergefield
.. press Shift-F9 to expose the field code. It should look something like
'{MERGEFIELD MyData}', where 'MyData' is your data field's name
.. delete everything between 'MyData' and the closing field brace
.. add ' \# $,0.00' after 'MyData', so that you end up with '{MERGEFIELD
MyData \# $,0.00}'
.. press F9 to update the field
.. run your mailmerge.

However, if you've already converted the values to percentages in Access
(heaven knows why), you can recover this in Word with just a little bit
more
work. In addition to the above, before updating the field:
.. select the 'MERGEFIELD MyData' string in your mergefield
.. press Ctrl-F9 to insert a new pair of field braces, so that you get
'{{MERGEFIELD MyData} \# $,0.00}'
.. insert an '=' sign between the first two field braces and '*100' after
the
third field brace, so that you get '{={MERGEFIELD MyData}*100 \# $,0.00}'

Cheers

--
macropod
[MVP - Microsoft Word]
PS: If you want $ without cents, make the picture switch '\# $,0'
PPS: Switching to a Mac would make no difference - the same situation
applies there too.


Shannon said:
So, did I just take off the "100*" and format all 90 fields to be percentages
with 0 decimals in Access for naught? Are you saying I'm going to have
to
re-do all that in Word? I had to do that for each field, because I
when I
selected more than one field in the query, it took away the format options.
It took a long time.

Now, will I have to make these switch changes in the table in Word
individually? I've never used one, but this nonsense makes me want to switch
to a mac. Please tell me how to cluster or select all the inserted
fields I
want and "switdh" them all in one command.

Shannon

:

See "Formatting Word fields with switches" on fellow MVP Graham
Mayor's
website at

http://www.gmayor.com/formatting_word_fields.htm


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on
a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
Greetings! I have created a very simple merge from a query in
Access that
merges into a table in Word. The problem is a field defined as currency in
Access does not "carry over" the formatting into the Word document.
I can
put
a dollar sign in front of the field $<<Amount>> but I am dealing
with
large
numbers and need the comma separators. Does anyone know how I can carry
over
the currency formatting from Access, OR correctly format the data field in
the Word table?
Thanks for any suggestions, Lacie
 
M

macropod

Hi Shannon,

You can simplify the updating process by toggling field code display on, via
Alt-F9, then using Copy/Paste to replicate the changes. Depending on whether
you needed only to add the picture switch or introduce the extra field
coding, it may be easier to copy & paste:
.. the field switch to all affected fields; or
.. a single recoded field to all required locations, then update the field
names

Cheers

--
macropod
[MVP - Microsoft Word]


Shannon said:
Macropod's suggestion is working. Thanks. It's just a pain to do this 90
times. Someone else prepared the Word survey with tables already set up,
otherwise, I could copy and paste the codes. Actually, I could have done
that this time, if I had not already entered all the field names into the
table.

macropod said:
For the currency, all you need to do is to add a picture switch to the
mergefield in you mailmerge template. To do this:
.. select the mergefield
.. press Shift-F9 to expose the field code. It should look something like
'{MERGEFIELD MyData}', where 'MyData' is your data field's name
.. delete everything between 'MyData' and the closing field brace
.. add ' \# $,0.00' after 'MyData', so that you end up with '{MERGEFIELD
MyData \# $,0.00}'
.. press F9 to update the field
.. run your mailmerge.

However, if you've already converted the values to percentages in Access
(heaven knows why), you can recover this in Word with just a little bit more
work. In addition to the above, before updating the field:
.. select the 'MERGEFIELD MyData' string in your mergefield
.. press Ctrl-F9 to insert a new pair of field braces, so that you get
'{{MERGEFIELD MyData} \# $,0.00}'
.. insert an '=' sign between the first two field braces and '*100' after the
third field brace, so that you get '{={MERGEFIELD MyData}*100 \# $,0.00}'

Cheers

--
macropod
[MVP - Microsoft Word]
PS: If you want $ without cents, make the picture switch '\# $,0'
PPS: Switching to a Mac would make no difference - the same situation
applies there too.


Shannon said:
So, did I just take off the "100*" and format all 90 fields to be percentages
with 0 decimals in Access for naught? Are you saying I'm going to have to
re-do all that in Word? I had to do that for each field, because I when I
selected more than one field in the query, it took away the format options.
It took a long time.

Now, will I have to make these switch changes in the table in Word
individually? I've never used one, but this nonsense makes me want to switch
to a mac. Please tell me how to cluster or select all the inserted
fields
I
want and "switdh" them all in one command.

Shannon

:

See "Formatting Word fields with switches" on fellow MVP Graham Mayor's
website at

http://www.gmayor.com/formatting_word_fields.htm
a
paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
Greetings! I have created a very simple merge from a query in
Access
that
merges into a table in Word. The problem is a field defined as currency in
Access does not "carry over" the formatting into the Word
document. I
can
put
a dollar sign in front of the field $<<Amount>> but I am dealing with
large
numbers and need the comma separators. Does anyone know how I can carry
over
the currency formatting from Access, OR correctly format the data field in
the Word table?
Thanks for any suggestions, Lacie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top