Access to Word

[MrH via AccessMonster.com]

Having a problem with getting the format correct in a Word document when
pulling the info from Access..... This is the common "Merge to Word" code.....
One of my fields in Access is currency. In Access I have it set to "currency"
decimal at 2.. BUT..... with this code.......

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="BkMark7"
WordObj.Selection.TypeText [txtBidAnnual]

It shows up in the Word document with 3 digits after the decimal.
So.... Question is... Where do I add the formating in Word to correct this?
And.... I actually would like to round this figure to nearest dollar when it
shows up in the Word document. Is there a way of doing that in Word? I am not
well versed at all in VBA for Word.... Thats my problem

Thanks

 

[Albert D. Kallal]

Base you data source on a query, and that way you can format anyway you
want.

So, in the query builder, go:


txtBidAnnualF:format(BidAnnual],""$#,#.##")

if you don't want the decimal places, then go:

"$#,#"

So, try the format, and then test view the query to see if it what you
want....

You likey could also format this in word, but then I don't really know how
to...

So, use the new formatted collum you make in place of the actual field.

 

[Guest]

Hi

Formatting in Access as suggested by Albert won't carry forward into Word -
it will still receive the 'raw' data.

So in Word, open the merge document and hit ALT + F9. This will reveal the
merge fields.

Add the following to the field you want to format:

\# $#,###.00

e.g. {MERGEFIELD "Field Name" \# $#,###.00}

That should do it.

Cheers.

BW

 

[Albert D. Kallal]

Hi

Formatting in Access as suggested by Albert won't carry forward into
Word -
it will still receive the 'raw' data.

If you look close, the OP is using bookmarks, and not merge fields.

So, the data source setting is occurring on the ms-access side..not from
word.

Thus, you can build any expression you want...even a VBA expressions, and
that expression will be what word receives.

You are correct if you use a vba expression in a query, then not only can't
word use that query, it will not even appear in the list of quires (and
tables) for the merge.

In this case however, the user is doing the merge from ms-access, and is
writing VBA code to actually search the word document, and replace book
marks (word merge fields are NOT being used).

 

[Guest]

Sorry Albert - you are quite correct. I should have read the whole post
before diving in!

I've solved this before in Word via VBA using bookmarks but not using the
'TypeText' method.

I use the range.InsertBefore method and it works well. e.g.

Set rngS1 = wdApp.ActiveDocument.Bookmarks("S1").Range
rngS1.InsertBefore Format(Nz("FundAmount", 0), "£#,##0.00")

Cheers, and appolgies again for doubting your words of wisdom!

BW

 

[MrH via AccessMonster.com]

Thank you all very much! Put me on the right track!
Couldn't use the formatting in a query....... The field is a calculated field
on a form.
And of course as you saw cant format using the "F9".... so altered the code
to

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="BkMark7"
WordObj.Selection.TypeText (Format(Nz([txtBidAnnual], 0), "$#,#"))

or

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="BkMark7"
WordObj.Selection.TypeText (Format(Nz([txtBidAnnual], 0), "$#,##0.00"))
for 2 decimal places..........

Works great!
Thank you again