By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

numbers not merging with commas and decimal places in mail merges

P: n/a
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
Nov 6 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Wed, 5 Nov 2008 21:10:14 -0800 (PST), DeZZar
<de**************@gmail.comwrote:

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

You would use:
select Format$(MyAmount, '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
Nov 6 '08 #2

P: n/a
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_Click()
On Error GoTo MergeButton2_Err
Dim objWord As Word.Application

Dim strTemplate As String
Dim strDoc As String

strTemplate = "X:\Leaselocks\Leaselock DB\Lease Lock
Agreement.doc"
strDoc = "X:\Leaselocks\" & Forms!frm_Leaselocks!GetDirectory

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

.ActiveDocument.Bookmarks("CustomerName").Select
.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("Address").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Address))
.ActiveDocument.Bookmarks("Suburb").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Suburb))
.ActiveDocument.Bookmarks("StateCust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!StateCust))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Postcode))
.ActiveDocument.Bookmarks("Trust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Trust))
.ActiveDocument.Bookmarks("Product").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Product))
.ActiveDocument.Bookmarks("SettlementDate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("Term").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Term))
.ActiveDocument.Bookmarks("Frequency").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Frequency))
.ActiveDocument.Bookmarks("Rentals").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Rentals))
.ActiveDocument.Bookmarks("Amount").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Amount))
.ActiveDocument.Bookmarks("Balloon_RV").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Balloon_RV))
.ActiveDocument.Bookmarks("Goods").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Goods))
.ActiveDocument.Bookmarks("CustomerRate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
CustomerRate))
.ActiveDocument.Bookmarks("SettlementDate2").Selec t
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("DocumentName").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!DocumentName)
& " ACN: " & Forms!frm_Leaselocks!ACN_ARBN)

objWord.ActiveDocument.Save

MsgBox "Leaselock document completed for " & Forms!frm_Leaselocks!
CustomerName

objWord.Quit

End With

Exit Sub
MergeButton2_Err:
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
Nov 7 '08 #3

P: n/a
On Thu, 6 Nov 2008 21:54:47 -0800 (PST), DeZZar
<de**************@gmail.comwrote:

OK, so you're reading the values from a form. Then apply code similar
to this:
..Selection.Text = Format$(Forms!frm_Leaselocks!Amount, "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_Click()
On Error GoTo MergeButton2_Err
Dim objWord As Word.Application

Dim strTemplate As String
Dim strDoc As String

strTemplate = "X:\Leaselocks\Leaselock DB\Lease Lock
Agreement.doc"
strDoc = "X:\Leaselocks\" & Forms!frm_Leaselocks!GetDirectory

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

.ActiveDocument.Bookmarks("CustomerName").Select
.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("Address").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Address))
.ActiveDocument.Bookmarks("Suburb").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Suburb))
.ActiveDocument.Bookmarks("StateCust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!StateCust))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Postcode))
.ActiveDocument.Bookmarks("Trust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Trust))
.ActiveDocument.Bookmarks("Product").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Product))
.ActiveDocument.Bookmarks("SettlementDate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("Term").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Term))
.ActiveDocument.Bookmarks("Frequency").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Frequency))
.ActiveDocument.Bookmarks("Rentals").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Rentals))
.ActiveDocument.Bookmarks("Amount").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Amount))
.ActiveDocument.Bookmarks("Balloon_RV").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Balloon_RV))
.ActiveDocument.Bookmarks("Goods").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Goods))
.ActiveDocument.Bookmarks("CustomerRate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
CustomerRate))
.ActiveDocument.Bookmarks("SettlementDate2").Selec t
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("DocumentName").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!DocumentName)
& " ACN: " & Forms!frm_Leaselocks!ACN_ARBN)

objWord.ActiveDocument.Save

MsgBox "Leaselock document completed for " & Forms!frm_Leaselocks!
CustomerName

objWord.Quit

End With

Exit Sub
MergeButton2_Err:
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
Nov 13 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.