Problems exporting to excel table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I make a output from access to excel, a field where I stored a number and characters together and when this field is exported to excel, what is showed in the EXCEL cell is not what I had input in the ACCESS cell and yet comes out a completely different number like for exemple what happend.When I input "1235-1" in the access , in the excel cell showed "-242884" .If there is someone that could help me explain why does it happen and some sort of solution to this case I appeciate. Thanks , JP
 
Hi JP,

Excel sometimes gets too clever for its own good in trying to make sense
of input from the user. Here it seems to be interpreting the "20a" as
"20 a.m." i.e. 20:00 military time - which Excel (and Access) stores
internally as 0.833333333. Probably you can confirm this by trying
values such as "6a" - which would correspond to 0.25 - and "6p" (0.75).

One way round this would be to modify the report to place an apostrophe
at the start of each affected field. E.g. if you have a textbox bound to
the field MyField, change its ControlSource from
MyField
to
="'"&[MyField]

The apostrophes will force Excel to treat the text as literal text and
not try to interpret it as some other kind of value.

Once in Excel you need to "edit" the data in each cell to hide the
apostrophy. If you double-click in a cell containing
'20a
and then click the green check mark or hit enter without actually
changing anything, Excel will interpret the apostrophe as a "flag" and
hide it. You can do the same from code with something like this:

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

See http://support.microsoft.com/?id=823222 for more
 
Back
Top