John Nurick said:Hi,
Excel sometimes gets to 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, but they won't show
up in the worksheet.
When i export a report to excel, any field that returns
the text 20a, get converted to 0.833333333333333.
Any ideas.
Thzx
This seems to be the problem, it is documented, but not in context with the
error i was having.
I have changed the ControlSource to include the ', but now excel does not
hide it. I am guessing that because when you export from an access 2000 DB
on access 2002 it still exports a old Excel that does not support
recognising the '.
John Nurick said:Hi,
Excel sometimes gets to 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, but they won't show
up in the worksheet.
When i export a report to excel, any field that returns
the text 20a, get converted to 0.833333333333333.
Any ideas.
Thzx
-----Original Message-----
Umm. This trick often works. You can force it to work by editing the
data once it's Excel: 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
Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en- us;291047
How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en- us;181298This seems to be the problem, it is documented, but not in context with the
error i was having.
I have changed the ControlSource to include the ', but now excel does not
hide it. I am guessing that because when you export from an access 2000 DB
on access 2002 it still exports a old Excel that does not support
recognising the '.
Hi,
Excel sometimes gets to 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, but they won't show
up in the worksheet.
On Mon, 17 May 2004 07:51:40 - 0700, "[email protected]"
When i export a report to excel, any field that returns
the text 20a, get converted to 0.833333333333333.
Any ideas.
Thzx
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
I will try to see if that works, could i change the way
it exports it.
I dont have the problem doing the export from an access 2
database (yes version 2).
This must be when Access 2k exports internaly.
-----Original Message-----
Umm. This trick often works. You can force it to work by editing the
data once it's Excel: 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
Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en- us;291047
How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en- us;181298This seems to be the problem, it is documented, but not in context with the
error i was having.
I have changed the ControlSource to include the ', but now excel does not
hide it. I am guessing that because when you export from an access 2000 DB
on access 2002 it still exports a old Excel that does not support
recognising the '.
Hi,
Excel sometimes gets to 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, but they won't show
up in the worksheet.
On Mon, 17 May 2004 07:51:40 - 0700, "[email protected]"
When i export a report to excel, any field that returns
the text 20a, get converted to 0.833333333333333.
Any ideas.
Thzx
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.