Decimal Problem - Access to Word

[worldcert]

Hello, I am having a problem with access/word. I have a database with an invoice page. When I print an invoice a word template opens and the data gets imported against bookmarks in Word.

However the problem is the data in access will be formated like this

500.00

and in Word like this

500

If I am printing multiple invoice then I have to manually type in the decimals.

How can I fix the database/template so the format is the same?

Thanks in advance.

 

[Richie P]

Hi worldcert,

Would you mind posting how you are exporting data to Word from Access - i am trying to do this with a variable number of form fields.

The way I currently do it is using the custom tab under the 'document properties' and defining the types. Is this how you're doing it, and if so, what about changing the data field to 'text' if you've got it set to 'number' which is probably rounding the input.

HTH
Rich

 

[Mile-O]

You'll need to use the Format() function at some point in your code in order to change the data being placed on your Word document.

 

[worldcert]

Hi Richie P

To extract my data I created a command button and on clicking it there is an event procedure. Here is the code.

Private Sub print_Click()
On Error GoTo Err_print_Click

Dim objword As Word.Application
Set objword = CreateObject("Word.Application")
With objword
.Visible = True
.documents.Open ("Z:\WCS DATABASE\WCSInvoice.doc")

.ActiveDocument.Bookmarks("companyname").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceCompany))
.ActiveDocument.Bookmarks("address").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceAddress))
.ActiveDocument.Bookmarks("city").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceCity))
.ActiveDocument.Bookmarks("county").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceCounty))
.ActiveDocument.Bookmarks("postcode").Select
.Selection.Text = (CStr(Forms!invoice!InvoicePostCode))
.ActiveDocument.Bookmarks("country").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceCountry))
.ActiveDocument.Bookmarks("invoiceno").Select
.Selection.Text = (CStr(Forms!invoice!InvoiceID))
.ActiveDocument.Bookmarks("date").Select
.Selection.Text = (CStr(Forms!invoice!Date))
.ActiveDocument.Bookmarks("orderno").Select
.Selection.Text = (CStr(Forms!invoice!ordernumber))
.ActiveDocument.Bookmarks("accountno").Select
.Selection.Text = (CStr(Forms!invoice!CustomerID))
.ActiveDocument.Bookmarks("currency").Select
.Selection.Text = (CStr(Forms!invoice!currency))
.ActiveDocument.Bookmarks("currency1").Select
.Selection.Text = (CStr(Forms!invoice!currency))
.ActiveDocument.Bookmarks("currency2").Select
.Selection.Text = (CStr(Forms!invoice!currency))
.ActiveDocument.Bookmarks("currency3").Select
.Selection.Text = (CStr(Forms!invoice!currency))
.ActiveDocument.Bookmarks("currency4").Select
.Selection.Text = (CStr(Forms!invoice!currency))
.ActiveDocument.Bookmarks("servicedetails").Select
.Selection.Text = (CStr(Forms!invoice!ServiceDetails))

If Not PO1PRICE = 0 Then
.ActiveDocument.Bookmarks("po1price").Select
.Selection.Text = (CStr(Forms!invoice!PO1PRICE))
End If

If Not VATPO1 = 0 Then
.ActiveDocument.Bookmarks("vatpo1").Select
.Selection.Text = (CStr(Forms!invoice!VATPO1))

End If

.ActiveDocument.Bookmarks("preaudit").Select
.Selection.Text = (CStr(Forms!invoice!ServiceDetailsPA))

If Not PAO1PRICE = 0 Then
.ActiveDocument.Bookmarks("pao1price").Select
.Selection.Text = (CStr(Forms!invoice!PAO1PRICE))
End If

If Not VATPAO1 = 0 Then
.ActiveDocument.Bookmarks("vatpao1").Select
.Selection.Text = (CStr(Forms!invoice!VATPAO1))
End If
.ActiveDocument.Bookmarks("assessment").Select
.Selection.Text = (CStr(Forms!invoice!ServiceDetailsAO))

If Not AO1PRICE = 0 Then
.ActiveDocument.Bookmarks("ao1price").Select
.Selection.Text = (CStr(Forms!invoice!AO1PRICE))
End If

If Not VATAO1 = 0 Then
.ActiveDocument.Bookmarks("vatao1").Select
.Selection.Text = (CStr(Forms!invoice!VATAO1))
End If
.ActiveDocument.Bookmarks("servicedetailso").Select
.Selection.Text = (CStr(Forms!invoice!ServiceDetailsSO))

If Not SO1PRICE = 0 Then
.ActiveDocument.Bookmarks("so1price").Select
.Selection.Text = (CStr(Forms!invoice!SO1PRICE))

End If


If Not VATSO1 = 0 Then
.ActiveDocument.Bookmarks("vatso1").Select
.Selection.Text = (CStr(Forms!invoice!VATSO1))
End If

If Not otherservices = 0 Then
.ActiveDocument.Bookmarks("otherservices").Select
.Selection.Text = (CStr(Forms!invoice!otherservices))
End If


If Not other = 0 Then
.ActiveDocument.Bookmarks("other").Select
.Selection.Text = (CStr(Forms!invoice!other))
End If

If Not vatother = 0 Then
.ActiveDocument.Bookmarks("vatother").Select
.Selection.Text = (CStr(Forms!invoice!VATOtherservices))

End If

.ActiveDocument.Bookmarks("tnet").Select
.Selection.Text = (CStr(Forms!invoice!test))
.ActiveDocument.Bookmarks("tvat").Select
.Selection.Text = (CStr(Forms!invoice!testvat))

.ActiveDocument.Bookmarks("total").Select
.Selection.Text = (CStr(Forms!invoice!total))

End With

Exit Sub

Err_print_Click:
If Err.number = 94 Then
objword.Selection.Text = ""
Resume Next
End If
Exit Sub

End Sub

Thanks for the reply SJ McAbney. Were do I put the Format function. In the code above ?