Report to Excel Export: String Converts to Double

[DarkSprout]

When I Export a report to Excel only a few out thousends of records change type:
One of which changes from aString: 20a to a Double: 0.833333333
and an other 03a to 0.125, and a third: 13a to 0.541666666666667

The string data comes from a tempTable with the field defind as TEXT(50)
they all have an 'a' in them, is this a clue

Help!
- With Thanks,

[rconverse]

What export command are you using? (Transfertext, spreadsheet)

[matthewspatrick]

The values appear to be converting to times.

How are you doing the export?

[DarkSprout]

I'm Using the Auto Export from the Print Preview CommandBar, the reason I'm using this button, is that - this is what the end user prefers to use when exporting and Report.

[asingh]

The '' will appear in front of exported data..from Access to excell. But it should not be converting the data types. After export..did you try to change the formats on the Excel sheet..??

[DarkSprout]

The '' will appear in front of exported data..from Access to excell. But it should not be converting the data types. After export..did you try to change the formats on the Excel sheet..??

By the time the data is in Excel - it's dead. The formating is the default General, and there is nothing to Cast it back to.

Hence By Problem.
=DarkSprout=

[DarkSprout]

Could some one please look at this, it's driving me potty.
With Thanks.

[rconverse]

The values appear to be converting to times.

How are you doing the export?

I concur.

You could workaround in Excel.

Add a column and add this formula to your Excel spreadsheet.

=ROUND(A1*24, 0) & "a" '<== where A1 is the cell with the converted value

Copy, paste as value and delete the old column.

[matthewspatrick]

I'd be inclined to export this via code...


Dim rs As DAO.Recordset
Dim xlApp As Object
Dim c As Long

Set rs = CurrentDB.OpenRecordset("SELECT * FROM SomeTable")
Set xlApp = CreateObject("Excel.Application")

With xlApp.ActiveSheet
'this is the column we need to force to text
.Range("C:C").NumberFormat = "@"
For c = 1 To rs.Fields.Count
.Cells(1, c) = rs.Fields(c - 1).Name
Next
.[a2].CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing

xlApp.Visible = True
Set xlApp = Nothing

[OTWarrior]

How about changing the control source field on the report that is calling the error to:


=StrConv([field],3)


instead of


[field]