Convert bookmark in word to currency in VBA access 2013

[sashapixie]

Hello All,

I have copied the following code from a book to merge data from a word document with a form in Access 2013, however the bookmark Tender_Total appears as a currency in my form but I cannot get this format to transfer over to the word document, I also have a similar formatting issue with the Letter_Date bookmark. I want the date to appear as 23rd November 2014, however this is appearing as 23/11/14. Again this is not the format on my form in Access.

I have been trying to work this out al day and now feeing very frustrated, I am eager to learn VBA, any tips would be greatly appreciated.

Private Sub Print_Letter_Click()
'Declare Variables
Dim sAccessAddress As String
Dim aAccessSalutation As String

'Build sAccessAddress
sAccessAddress = First_Name & " " & Last_Name & _
vbCrLf & Company & vbCrLf & Street_Name & vbCrLf & Town & vbCrLf _
& County & " " & vbCrLf & Postcode

'Build sAccessSalutation
sAccessSalutation = First_Name & " " & Last_Name

'Build sAccessTender_Total
sAccessTender_Total = Tender_Total

'Build sAccessStart_Date
sAccessStart_Date = Start_Date

'Build sAccessEnd_Date
sAccessEnd_Date = End_Date

'Build sAccessLetter_Date
sAccessLetter_Date = Letter_Date

'Build sAccessManager_Name
sAccessManager_Name = Manager_Name

'Build sAccessContrators_Phone
sAccessContractors_Phone = Contractors_Phone

'Build sAccessDirector
sAccessDirector = Director

'Build sAccessDirectors_Title
sAccessDirectors_Title = Directors_Title

'Declare and set Word object variables
'Dim Wrd As New Word.Application
Dim Wrd As Object
Set Wrd = CreateObject("Word.Application")

'Specify Path to Template
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & _
"\AccessAddress"

'Open Word using template and make Word visible
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True

'Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks
.Item("Letter_Date").Range.Text = sAccessLetter_Date
.Item("AccessAddress").Range.Text = sAccessAddress
.Item("AccessSalutation").Range.Text = sAccessSalutation
.Item("Tender_Total").Range.Text = sAccessTender_Total
.Item("Start_Date").Range.Text = sAccessStart_Date
.Item("End_Date").Range.Text = sAccessEnd_Date
.Item("Manager_Name").Range.Text = sAccessManager_Name
.Item("Contractors_Phone").Range.Text = sAccessContractors_Phone
.Item("Director").Range.Text = sAccessDirector
.Item("Directors_Title").Range.Text = sAccessDirectors_Title

End With

''Open in Print Preview mode, let user print
'Wrd.ActiveDocument.PrintPreview

'Clean Up code
Set Wrd = Nothing

End Sub

 

[sashapixie]

I have found the solution on a different thread.

 

[SydneyGeek]

Hi sashapixie,

Would you be able to put a link to that solution here please? It would be helpful for users who come across this thread in a search.

Denis