numbers not merging with commas and decimal places in mail merges

[DeZZar]

Hi All,

I have a database that passes form data to both emails and to word
documents.

The problem I have is that when the email or merge is instructed
access only seems to pass the basic number string.

For example if my form displays $5,650.00 the resultant merged
document shows 5650 only.

I have the format table set to "Currency" and the form set to
currency but neither seems to affect the result.

Does anyone have a solution for this? I'm not concerned that the $
sybol isn't passed but I would like the comma and decimal passed.

(note: it does pass the decimal if there is a value. $5,650.45 shows
as 5650.45)

Cheers
DeZZar

[Tom van Stiphout]

Re: numbers not merging with commas and decimal places in mail merges

On Wed, 5 Nov 2008 21:10:14 -0800 (PST), DeZZar
<derrick.goostr ey@gmail.comwro te:

You could pass formatted data to those procedures. So rather than:
select MyAmount
from MyTable

You would use:
select Format$(MyAmoun t, 'Currency')
from MyTable

-Tom.
Microsoft Access MVP

>Hi All,
>
>I have a database that passes form data to both emails and to word
>documents.
>
>The problem I have is that when the email or merge is instructed
>access only seems to pass the basic number string.
>
>For example if my form displays $5,650.00 the resultant merged
>document shows 5650 only.
>
>I have the format table set to "Currency" and the form set to
>currency but neither seems to affect the result.
>
>Does anyone have a solution for this? I'm not concerned that the $
>sybol isn't passed but I would like the comma and decimal passed.
>
>(note: it does pass the decimal if there is a value. $5,650.45 shows
>as 5650.45)
>
>Cheers
>DeZZar

[DeZZar]

Re: numbers not merging with commas and decimal places in mail merges

Hi Tom,

This is my mail merge code for creating a new document based on a
template.

How would I incorporate the format in this?

<XXXXX CODE START XXXX>
Private Sub MergeButton2_Cl ick()
On Error GoTo MergeButton2_Er r
Dim objWord As Word.Applicatio n

Dim strTemplate As String
Dim strDoc As String

strTemplate = "X:\Leaselocks\ Leaselock DB\Lease Lock
Agreement.doc"
strDoc = "X:\Leaselocks\ " & Forms!frm_Lease locks!GetDirect ory

FileCopy strTemplate, strDoc
Set objWord = CreateObject("W ord.Application ")
With objWord
.Visible = False
.Documents.Open (strDoc)

.ActiveDocument .Bookmarks("Cus tomerName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerName))
.ActiveDocument .Bookmarks("ABN ").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ABN ))
.ActiveDocument .Bookmarks("ACN _ARBN").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ACN _ARBN))
.ActiveDocument .Bookmarks("Add ress").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Add ress))
.ActiveDocument .Bookmarks("Sub urb").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sub urb))
.ActiveDocument .Bookmarks("Sta teCust").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sta teCust))
.ActiveDocument .Bookmarks("Pos tcode").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pos tcode))
.ActiveDocument .Bookmarks("Tru st").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Tru st))
.ActiveDocument .Bookmarks("Pro duct").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pro duct))
.ActiveDocument .Bookmarks("Set tlementDate").S elect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
.ActiveDocument .Bookmarks("Ter m").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ter m))
.ActiveDocument .Bookmarks("Fre quency").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Fre quency))
.ActiveDocument .Bookmarks("Ren tals").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ren tals))
.ActiveDocument .Bookmarks("Amo unt").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Amo unt))
.ActiveDocument .Bookmarks("Bal loon_RV").Selec t
.Selection.Text = (CStr(Forms!frm _Leaselocks!Bal loon_RV))
.ActiveDocument .Bookmarks("Goo ds").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Goo ds))
.ActiveDocument .Bookmarks("Cus tomerRate").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerRate))
.ActiveDocument .Bookmarks("Set tlementDate2"). Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
.ActiveDocument .Bookmarks("Doc umentName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!Doc umentName)
& " ACN: " & Forms!frm_Lease locks!ACN_ARBN)

objWord.ActiveD ocument.Save

MsgBox "Leaselock document completed for " & Forms!frm_Lease locks!
CustomerName

objWord.Quit

End With

Exit Sub
MergeButton2_Er r:
If Err.Number = 94 Then
objWord.Selecti on.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub

[Tom van Stiphout]

Re: numbers not merging with commas and decimal places in mail merges

On Thu, 6 Nov 2008 21:54:47 -0800 (PST), DeZZar
<derrick.goostr ey@gmail.comwro te:

OK, so you're reading the values from a form. Then apply code similar
to this:
..Selection.Tex t = Format$(Forms!f rm_Leaselocks!A mount, "Currency")

-Tom.
Microsoft Access MVP

>Hi Tom,
>
>This is my mail merge code for creating a new document based on a
>template.
>
>How would I incorporate the format in this?
>
><XXXXX CODE START XXXX>
>Private Sub MergeButton2_Cl ick()
On Error GoTo MergeButton2_Er r
Dim objWord As Word.Applicatio n
>
Dim strTemplate As String
Dim strDoc As String
>
strTemplate = "X:\Leaselocks\ Leaselock DB\Lease Lock
>Agreement.do c"
strDoc = "X:\Leaselocks\ " & Forms!frm_Lease locks!GetDirect ory
>
FileCopy strTemplate, strDoc
Set objWord = CreateObject("W ord.Application ")
With objWord
.Visible = False
.Documents.Open (strDoc)
>
.ActiveDocument .Bookmarks("Cus tomerName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
>CustomerName ))
.ActiveDocument .Bookmarks("ABN ").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ABN ))
.ActiveDocument .Bookmarks("ACN _ARBN").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ACN _ARBN))
.ActiveDocument .Bookmarks("Add ress").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Add ress))
.ActiveDocument .Bookmarks("Sub urb").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sub urb))
.ActiveDocument .Bookmarks("Sta teCust").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sta teCust))
.ActiveDocument .Bookmarks("Pos tcode").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pos tcode))
.ActiveDocument .Bookmarks("Tru st").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Tru st))
.ActiveDocument .Bookmarks("Pro duct").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pro duct))
.ActiveDocument .Bookmarks("Set tlementDate").S elect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
>SettlementDate ))
.ActiveDocument .Bookmarks("Ter m").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ter m))
.ActiveDocument .Bookmarks("Fre quency").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Fre quency))
.ActiveDocument .Bookmarks("Ren tals").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ren tals))
.ActiveDocument .Bookmarks("Amo unt").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Amo unt))
.ActiveDocument .Bookmarks("Bal loon_RV").Selec t
.Selection.Text = (CStr(Forms!frm _Leaselocks!Bal loon_RV))
.ActiveDocument .Bookmarks("Goo ds").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Goo ds))
.ActiveDocument .Bookmarks("Cus tomerRate").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
>CustomerRate ))
.ActiveDocument .Bookmarks("Set tlementDate2"). Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!
>SettlementDate ))
.ActiveDocument .Bookmarks("Doc umentName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!Doc umentName)
>& " ACN: " & Forms!frm_Lease locks!ACN_ARBN)
>
>objWord.Active Document.Save
>
>MsgBox "Leaselock document completed for " & Forms!frm_Lease locks!
>CustomerName
>
>objWord.Quit
>
>End With
>
Exit Sub
>MergeButton2_E rr:
If Err.Number = 94 Then
objWord.Selecti on.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
>End Sub