no way of formatting a column of data imported into word document from access to be currenc

[Abacus1234]

Using the following code, I can pull multiple records into a table in word from Access. But I can find no way of formatting a column that was currency, back to currency after pulling the data in.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim intRecords As Integer
Dim intColumns As Integer
strSQL = "QTopLevelPage2a"
Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.EOF = TrueThen
MsgBox"No records were retrieved. Cannot continue.", vbCritical,"Request Aborted"
rs.Close
Set db =Nothing
Exit Sub
End If
'lets get some counts
rs.MoveLast
intRecords = 0
intRecords = rs.RecordCount
Debug.Print "rs.RecordCount = " & intRecords

'Name the Range for the data added
intRecords = intRecords '+ 1 'add one row for the headernames


'lets see how many columns we have
intColumns = 0
intColumns = rs.Fields.Count

'*************************************


Dim myWordApp As Word.Application
Dim docNew As Word.Document


Dim docTable As Word.Table

Set myWordApp = CreateObject("Word.Application")
myWordApp.Visible = True
Set docNew = myWordApp.Documents.Open("C:\Test\WordDocFolder\Te stMailMerge.doc")

'Create a table that has the correct number of cells

docNew.Tables.Add Range:=docNew.Range(Start:=0, End:=0),NumRows:=intRecords, NumColumns:=intColumns

Set docTable = docNew.Tables(1)
'Get some header names in the first Row
For i = 1 Tors.Fields.Count
docTable.Cell(1, i).Range.text = rs.Fields(i - 1) '.Name
Next i
rs.MoveLast
While rs.BOF = False
'Populate the last row
For i = 1 Tors.Fields.Count
If NotIsNull(rs.Fields(i - 1).Value) Then
docTable.Cell(intRecords, i).Range.text =rs.Fields(i - 1) '.Value
End If
Next i
Debug.Print intRecords
intRecords = intRecords - 1
rs.MovePrevious
Wend


' Start with the second column.

docTable.Columns(2).Select

'Extend acrossthe specified count of columns.
myWordApp.Selection.MoveRight Unit:=wdCharacter, _
Count:=1,Extend:=wdExtend
myWordApp.Selection.NumberFormat = "$#.##00.00"


'docNew.Activate
'docNew.PrintPreview
docNew.Save
docNew.Close
myWordApp.Quit
Set docNew = Nothing
Set myWordApp = Nothing
End Sub

[June7]

Is NumberFormat a property in Word? If it can't be done manually I doubt it can be done programmatically. I don't see currency formatting options in Word table setup.

Might have to import the values concatenated with $ character or using Format function in Access query. Then the question is how to set decimal tab or right tab for aligning data within the Word table.

[ItsMe]

Like June mentioned, limited options with the Word table. I did look and see some formatting options in VBA's intellisense. Just not for data types. I think the best approach would be the format in SQL option. Could edit your query object or bring the SQL from query object into VBA, adding formatting to an alias.

[Abacus1234]

I can add the $ after the value, but do not know how to do it before the value.

[June7]

Options:

"$" & [fieldname]

Format([fieldname], "Currency")

[Abacus1234]

Thanks for the information. I really appreciate it.

This is the code I am using to pull in the data. All I know how to do at the moment is reference Column 2 and use the property autofit, and align right. I don't have any FieldNames to reference. I see the reference to rs.Fields(i - 1).Name but don't know how to declare or use FieldNames for each column. I am used to having documents with individual field names and applying formatting to them, but don't know how to handle columns of data.

Set docTable = docNew.Tables(1)
'Get some header names in the first Row
For i = 1 To rs.Fields.Count
docTable.Cell(1, i).Range.text = rs.Fields(i - 1).Name
Next i
rs.MoveLast
While rs.BOF = False
'Populate the last row
For i = 1 Tors.Fields.Count
If NotIsNull(rs.Fields(i - 1).Value) Then
docTable.Cell(intRecords, i).Range.text = rs.Fields(i - 1).Value
End If
Next i
Debug.Print intRecords
intRecords = intRecords - 1



rs.MovePrevious

My data looks like this

5000
7000
2222
2000
576
350
300
300
657.54
475
1922
150
250
231
100

[ItsMe]

You can do it here
strSQL = "QTopLevelPage2a"


or you can edit the SQL in the query object named QTopLevelPage2a

[Abacus1234]

Thank-you, at last a small piece of the puzzle that I am familiar with. The data is now pulled over with $ and .00 where appropriate. A real step forward.

Thanks very much.

[ItsMe]

OK, let us know...