point me in the right direction please

[slescure]

I hope someone can point me in the right direction - i don't know a lot
of VBA so i'm stumbling around a bit.

I populate the recordset in the code below (rec) via an SQL statement.
I then create a table in a word document. Everything is fine - EXCEPT
- the field in the last column is a numeric field on the database
(mainframe) and it gets formatted as text in the resulting Word table -
the weird thing is that zeros after the decimal point are dropped and
so is the decimal point (i.e. 50.00 becomes 50 - if there's a non-zero
value after the decimal everything is fine - i.e. 89.09 comes out just
fine).

any suggestions?

thanks!

Private Sub CommandButton1_Click()

If Not rec.EOF Then

txt = txt & rec.GetString( _
ColumnDelimeter:=vbTab, _
RowDelimeter:=vbCrLf, _
NullExpr:="<null>")

'make a range at the end of the Word document
Set new_range = ActiveDocument.Range
new_range.Collapse wdCollapseEnd

' insert the text and convert it to a tabel
new_range.InsertAfter txt
new_range.ConvertToTable vbTab

' autofit to the contents
new_range.Tables(1).AutoFitBehavior wdAutoFitContent

' format the table
' new_range.Tables(1).AutoFormat Format:=wdTableFormatColorful2,
ApplyBorders:=True
new_range.Tables(1).AutoFormat Format:=wdTableFormatGrid1,
ApplyBorders:=True, AutoFit:=True, ApplyHeadingRows:=False

' add a blank line
Set new_range = ActiveDocument.Range
new_range.Collapse wdCollapseEnd
new_range.InsertParagraph
new_range.Collapse wdCollapseEnd
new_range.InsertParagraph
Else
MsgBox "No records found for owner", vbCritical
End If

End Sub

 

[Doug Robbins]

Use the Format() function to format the output

Format(whateveritis, "#,###.00")

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP