> I have been lurking and haven't found my answers so forgive me if this is
a
> bit newbie. Also forgive the coding if it is bad. It does work however.

> 1. I am doing some automation from Access 97 to Word 97. All works good
> except when moving a currency value. The automation does not input the
> decimal and 2 places past the decimal. For example if the value in the
> table/query is "$10.00", all the automation moves over is "10". I can
input
> the dollar sign in the Word template just before the bookmark but is there
a
> way to get the automation to put in the decimal and 2 places past? (Also
if
> the value is "$10.10" I get "10.1")

> 2. Similar to #1 - Can I format the Zip and Social Sec Number to include
the
> hyphen where applicable?

> 3. Can I tell automation to skip a bookmark if the table/query field is
> blank?

> I would like to be able to have it print automatically, but can't since
the
> user usually has to make a few formating changes before printing. Here is
> the code I am using now, if you have other suggestions, feel free....

> Public Sub ParticipantLetter(DistributionID As Long)

> Dim dbC                 As Database
> Dim qryDistribution     As QueryDef
> Dim recDistribution     As Recordset
> Dim objWord             As Word.Application     ' word object
> Dim objDoc              As Object               ' document object

> Set dbC = CurrentDb()
> Set qryDistribution = dbC.QueryDefs("Distributions-Export")
> qryDistribution.Parameters("PID") = DistributionID
> Set recDistribution = qryDistribution.OpenRecordset()
> If recDistribution.EOF Then
>     MsgBox "The Distribution with an ID of " & DistributionID & " could
not
> be found.", _
>        vbCritical, "Distribution Not Found"
>     Exit Sub
> End If

> Set objWord = New Word.Application
> objWord.Visible = True
> Set objDoc =
> objWord.Documents.Add("G:\Data\Database\Templates\ParticipantForms.dot")

> InsertTextAtBookmark objWord, objDoc, "J", recDistribution("Plan Name")
> InsertTextAtBookmark objWord, objDoc, "A", recDistribution("FirstName")
> InsertTextAtBookmark objWord, objDoc, "B", recDistribution("LastName")
> InsertTextAtBookmark objWord, objDoc, "C", recDistribution("Address1")
> InsertTextAtBookmark objWord, objDoc, "E", recDistribution("City")
> InsertTextAtBookmark objWord, objDoc, "F", recDistribution("State")
> InsertTextAtBookmark objWord, objDoc, "G", recDistribution("Zip")
> InsertTextAtBookmark objWord, objDoc, "H", recDistribution("FirstName")
> InsertTextAtBookmark objWord, objDoc, "I", recDistribution("LastName")

> InsertTextAtBookmark objWord, objDoc, "B2", recDistribution("Plan Name")
> InsertTextAtBookmark objWord, objDoc, "W", recDistribution("FirstName")
> InsertTextAtBookmark objWord, objDoc, "X", recDistribution("LastName")
> InsertTextAtBookmark objWord, objDoc, "Y", recDistribution("Address1")
> InsertTextAtBookmark objWord, objDoc, "A1", recDistribution("City")
> InsertTextAtBookmark objWord, objDoc, "A2", recDistribution("State")
> InsertTextAtBookmark objWord, objDoc, "A3", recDistribution("Zip")
> InsertTextAtBookmark objWord, objDoc, "A4",
> recDistribution("SocialSecurityNumber")
> InsertTextAtBookmark objWord, objDoc, "A5", recDistribution("HomePhone")

> InsertTextAtBookmark objWord, objDoc, "B3", recDistribution("Plan Name")
> InsertTextAtBookmark objWord, objDoc, "A6", recDistribution("FirstName")
> InsertTextAtBookmark objWord, objDoc, "A7", recDistribution("LastName")
> InsertTextAtBookmark objWord, objDoc, "A8",
> recDistribution("SocialSecurityNumber")

> InsertTextAtBookmark objWord, objDoc, "A9", recDistribution("FirstName")
> InsertTextAtBookmark objWord, objDoc, "B1", recDistribution("LastName")

> objDoc.SaveAs "C:\My Documents\DistributionFormsParticipant"

> End Sub

> Hi,

> 1.  Use the Format function to format the data before inserting it into
the
> document. For example (assuming Premium is a currency field), use
>   InsertTextAtBookmark objWord, objDoc, "ZZZ",
> Format$(recDistribution("Premium"), "Currency")
> rather than
>   InsertTextAtBookmark objWord, objDoc, "ZZZ", recDistribution("Premium")

> 2.  Same principle.

> 3.  Use IsNull to test whether a field is empty, e.g.:
>   If Not IsNull(recDistribution("Plan Name")) Then
>     InsertTextAtBookmark objWord, objDoc, "J", recDistribution("Plan
Name")
>   Endif

> ----
> Simon Lewis

> > I have been lurking and haven't found my answers so forgive me if this
is
> a
> > bit newbie. Also forgive the coding if it is bad. It does work however.

> > 1. I am doing some automation from Access 97 to Word 97. All works good
> > except when moving a currency value. The automation does not input the
> > decimal and 2 places past the decimal. For example if the value in the
> > table/query is "$10.00", all the automation moves over is "10". I can
> input
> > the dollar sign in the Word template just before the bookmark but is
there
> a
> > way to get the automation to put in the decimal and 2 places past? (Also
> if
> > the value is "$10.10" I get "10.1")

> > 2. Similar to #1 - Can I format the Zip and Social Sec Number to include
> the
> > hyphen where applicable?

> > 3. Can I tell automation to skip a bookmark if the table/query field is
> > blank?

> > I would like to be able to have it print automatically, but can't since
> the
> > user usually has to make a few formating changes before printing. Here
is
> > the code I am using now, if you have other suggestions, feel free....

> > Public Sub ParticipantLetter(DistributionID As Long)

> > Dim dbC                 As Database
> > Dim qryDistribution     As QueryDef
> > Dim recDistribution     As Recordset
> > Dim objWord             As Word.Application     ' word object
> > Dim objDoc              As Object               ' document object

> > Set dbC = CurrentDb()
> > Set qryDistribution = dbC.QueryDefs("Distributions-Export")
> > qryDistribution.Parameters("PID") = DistributionID
> > Set recDistribution = qryDistribution.OpenRecordset()
> > If recDistribution.EOF Then
> >     MsgBox "The Distribution with an ID of " & DistributionID & " could
> not
> > be found.", _
> >        vbCritical, "Distribution Not Found"
> >     Exit Sub
> > End If

> > Set objWord = New Word.Application
> > objWord.Visible = True
> > Set objDoc =
> > objWord.Documents.Add("G:\Data\Database\Templates\ParticipantForms.dot")

> > InsertTextAtBookmark objWord, objDoc, "J", recDistribution("Plan Name")
> > InsertTextAtBookmark objWord, objDoc, "A", recDistribution("FirstName")
> > InsertTextAtBookmark objWord, objDoc, "B", recDistribution("LastName")
> > InsertTextAtBookmark objWord, objDoc, "C", recDistribution("Address1")
> > InsertTextAtBookmark objWord, objDoc, "E", recDistribution("City")
> > InsertTextAtBookmark objWord, objDoc, "F", recDistribution("State")
> > InsertTextAtBookmark objWord, objDoc, "G", recDistribution("Zip")
> > InsertTextAtBookmark objWord, objDoc, "H", recDistribution("FirstName")
> > InsertTextAtBookmark objWord, objDoc, "I", recDistribution("LastName")

> > InsertTextAtBookmark objWord, objDoc, "B2", recDistribution("Plan Name")
> > InsertTextAtBookmark objWord, objDoc, "W", recDistribution("FirstName")
> > InsertTextAtBookmark objWord, objDoc, "X", recDistribution("LastName")
> > InsertTextAtBookmark objWord, objDoc, "Y", recDistribution("Address1")
> > InsertTextAtBookmark objWord, objDoc, "A1", recDistribution("City")
> > InsertTextAtBookmark objWord, objDoc, "A2", recDistribution("State")
> > InsertTextAtBookmark objWord, objDoc, "A3", recDistribution("Zip")
> > InsertTextAtBookmark objWord, objDoc, "A4",
> > recDistribution("SocialSecurityNumber")
> > InsertTextAtBookmark objWord, objDoc, "A5", recDistribution("HomePhone")

> > InsertTextAtBookmark objWord, objDoc, "B3", recDistribution("Plan Name")
> > InsertTextAtBookmark objWord, objDoc, "A6", recDistribution("FirstName")
> > InsertTextAtBookmark objWord, objDoc, "A7", recDistribution("LastName")
> > InsertTextAtBookmark objWord, objDoc, "A8",
> > recDistribution("SocialSecurityNumber")

> > InsertTextAtBookmark objWord, objDoc, "A9", recDistribution("FirstName")
> > InsertTextAtBookmark objWord, objDoc, "B1", recDistribution("LastName")

> > objDoc.SaveAs "C:\My Documents\DistributionFormsParticipant"

> > End Sub