Format numbers when executing sql

[Anthonynz]

Greetings. I am using VB6 to retrieve data from some foxpro tables as follows.

Dim cnCompany As New ADODB.Connection
Dim RsExport As ADODB.Recordset

strSQL = "Select price from Products"
Set RsExport = cnCompany.Execute(strSQL)

This works fine, but I would like to format the price. I have tried various including...

strSQL = "Select format(price,'0.00') from Products"
Set RsExport = cnCompany.Execute(strSQL)

but it gives the error message
format.prg does not exist

Does anyone know what will work with ADODB?

[Doogle]

The Format is only appropriate when you wish to display the value.

strSQL = "Select price from Products"
Set RsExport = cnCompany.Execute(strSQL)
Debug.Print Format(RsExport![price],"##0.##")

would print the value formatted.

[Anthonynz]

No good to me sorry. My next line
wksReport.Range("A2").CopyFromRecordset RsExport

populates an excel spreadsheet with whatever data is in the recordset, so need to format it in the selection. The Format command used to work with a foxpro recordset, but I needed to change to ADODB and it seems that format does not work with it.

[Doogle]

The only thing I can suggest is to format the target Cell Range in Excel. I'm no expert but something like

wksReport.Range("A2").NumberFormat = "##0.##"

might work.
(If it doesn't, then I suggest you google 'Excel NumberFormat' and see if you can come upon some examples.)