Text fields in Access reports garbled when exported to Excel

[Brian]

I have compiled a text field in Access reports from numbers (cricket team
score such as 150-7 with appropriate spacing) but when this is transferred to
Access the field is converted to a number (date?). Should the text be
compiled at Query stage?

 

[Ken Snell [MVP]]

You need to tell EXCEL that the text should be handled as text. Easiest way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]

 

[Brian]

Unfortunately I do not want ' at the head of fields when I print them direct
from Accesss. Thanks for the reply.
--
Brian Box

You need to tell EXCEL that the text should be handled as text. Easiest way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers (cricket team
score such as 150-7 with appropriate spacing) but when this is transferred
to
Access the field is converted to a number (date?). Should the text be
compiled at Query stage?

 

[Ken Snell [MVP]]

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is... Other
way is to use Automation to manipulate EXCEL after you write the data into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>

Unfortunately I do not want ' at the head of fields when I print them
direct
from Accesss. Thanks for the reply.
--
Brian Box

You need to tell EXCEL that the text should be handled as text. Easiest
way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers (cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the text be
compiled at Query stage?

 

[Brian]

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to Excel.

--
Brian Box

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is... Other
way is to use Automation to manipulate EXCEL after you write the data into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>

Unfortunately I do not want ' at the head of fields when I print them
direct
from Accesss. Thanks for the reply.
--
Brian Box

You need to tell EXCEL that the text should be handled as text. Easiest
way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers (cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the text be
compiled at Query stage?

 

[Ken Snell [MVP]]

The string that gets into the EXCEL cell will have the ' character at the
beginning (this is what I'd said), but the EXCEL spreadsheet does not print
it nor display it as part of the value. It is an indicator to EXCEL that the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export, then
the report will show a ' character at the beginning of the value -- ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to Excel.

--
Brian Box

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is...
Other
way is to use Automation to manipulate EXCEL after you write the data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>

Unfortunately I do not want ' at the head of fields when I print them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers (cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the text
be
compiled at Query stage?

 

[Brian]

When I ran the sequence that I described the ' character was preserved in
Excel. Sometimes these systems appear to be too clever. All I need is to be
able to designate a field as a text and have it stay that way without the
system trying to convert it to something else. Pre 1900 dates get converted
to negative numbers as well.
--
Brian Box

The string that gets into the EXCEL cell will have the ' character at the
beginning (this is what I'd said), but the EXCEL spreadsheet does not print
it nor display it as part of the value. It is an indicator to EXCEL that the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export, then
the report will show a ' character at the beginning of the value -- ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to Excel.

--
Brian Box

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is...
Other
way is to use Automation to manipulate EXCEL after you write the data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>


Unfortunately I do not want ' at the head of fields when I print them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've prepended a '
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers (cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the text
be
compiled at Query stage?

 

[Ken Snell [MVP]]

You might try casting the output field in a CStr function.... if that
doesn't work, then you may need to use Automation for the text string.

Regarding the date, you'll need to cast that field with the
Format([DateFieldName], "mm/dd/yyyy") function.
--

Ken Snell
<MS ACCESS MVP>

When I ran the sequence that I described the ' character was preserved in
Excel. Sometimes these systems appear to be too clever. All I need is to
be
able to designate a field as a text and have it stay that way without the
system trying to convert it to something else. Pre 1900 dates get
converted
to negative numbers as well.
--
Brian Box

The string that gets into the EXCEL cell will have the ' character at the
beginning (this is what I'd said), but the EXCEL spreadsheet does not
print
it nor display it as part of the value. It is an indicator to EXCEL that
the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export,
then
the report will show a ' character at the beginning of the value --
ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to
Excel.

--
Brian Box


:

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is...
Other
way is to use Automation to manipulate EXCEL after you write the data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>


Unfortunately I do not want ' at the head of fields when I print
them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've prepended a
'
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers
(cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the
text
be
compiled at Query stage?

 

[Brian]

Thanks for the tips - I will try them in a few days time and let you know the
outcome. As a self-taught Access/Excel user my experience is limited. not
heard of Automation before.
--
Brian Box

You might try casting the output field in a CStr function.... if that
doesn't work, then you may need to use Automation for the text string.

Regarding the date, you'll need to cast that field with the
Format([DateFieldName], "mm/dd/yyyy") function.
--

Ken Snell
<MS ACCESS MVP>

When I ran the sequence that I described the ' character was preserved in
Excel. Sometimes these systems appear to be too clever. All I need is to
be
able to designate a field as a text and have it stay that way without the
system trying to convert it to something else. Pre 1900 dates get
converted
to negative numbers as well.
--
Brian Box

The string that gets into the EXCEL cell will have the ' character at the
beginning (this is what I'd said), but the EXCEL spreadsheet does not
print
it nor display it as part of the value. It is an indicator to EXCEL that
the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export,
then
the report will show a ' character at the beginning of the value --
ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to
Excel.

--
Brian Box


:

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is...
Other
way is to use Automation to manipulate EXCEL after you write the data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>


Unfortunately I do not want ' at the head of fields when I print
them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've prepended a
'
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers
(cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the
text
be
compiled at Query stage?

 

[Brian]

Tried prepending the ', using Str & CStr put Excel retained it. Have inserted
extra field in Query to add ' if output to go to Excel, can then remove it by
string function.

Thanks for your help.
--
Brian Box

You might try casting the output field in a CStr function.... if that
doesn't work, then you may need to use Automation for the text string.

Regarding the date, you'll need to cast that field with the
Format([DateFieldName], "mm/dd/yyyy") function.
--

Ken Snell
<MS ACCESS MVP>

When I ran the sequence that I described the ' character was preserved in
Excel. Sometimes these systems appear to be too clever. All I need is to
be
able to designate a field as a text and have it stay that way without the
system trying to convert it to something else. Pre 1900 dates get
converted
to negative numbers as well.
--
Brian Box

The string that gets into the EXCEL cell will have the ' character at the
beginning (this is what I'd said), but the EXCEL spreadsheet does not
print
it nor display it as part of the value. It is an indicator to EXCEL that
the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export,
then
the report will show a ' character at the beginning of the value --
ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" & Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to
Excel.

--
Brian Box


:

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it is...
Other
way is to use Automation to manipulate EXCEL after you write the data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>


Unfortunately I do not want ' at the head of fields when I print
them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've prepended a
'
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers
(cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the
text
be
compiled at Query stage?

 

[Ken Snell [MVP]]

Thanks for posting the solution. I've never had this trouble in my
applications before, so it's good to have for future reference.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ', using Str & CStr put Excel retained it. Have
inserted
extra field in Query to add ' if output to go to Excel, can then remove it
by
string function.

Thanks for your help.
--
Brian Box

You might try casting the output field in a CStr function.... if that
doesn't work, then you may need to use Automation for the text string.

Regarding the date, you'll need to cast that field with the
Format([DateFieldName], "mm/dd/yyyy") function.
--

Ken Snell
<MS ACCESS MVP>

When I ran the sequence that I described the ' character was preserved
in
Excel. Sometimes these systems appear to be too clever. All I need is
to
be
able to designate a field as a text and have it stay that way without
the
system trying to convert it to something else. Pre 1900 dates get
converted
to negative numbers as well.
--
Brian Box


:

The string that gets into the EXCEL cell will have the ' character at
the
beginning (this is what I'd said), but the EXCEL spreadsheet does not
print
it nor display it as part of the value. It is an indicator to EXCEL
that
the
value in that cell is text, not numeric.

If you use the same query for a report as you do for the EXCEL export,
then
the report will show a ' character at the beginning of the value --
ACCESS
does not use ' for that purpose, just EXCEL does.

--

Ken Snell
<MS ACCESS MVP>

Tried prepending the ' but this was copied through by Excel.

Added two fields in the query :

HST: IIf([Wkts]=10,Str$([Score]),Str$([Score]) & "-" &
Format([Wkts]))
HST2: "'" & [HST]

but these retained the ' character on the report and when sent to
Excel.

--
Brian Box


:

The ' character will not be printed or visible in EXCEL unless you
double-click in the cell to edit the data.

Unfortunately, EXCEL sees the data and guesses at what type it
is...
Other
way is to use Automation to manipulate EXCEL after you write the
data
into
the worksheet; then you can change the data type in the cells.


--

Ken Snell
<MS ACCESS MVP>


Unfortunately I do not want ' at the head of fields when I print
them
direct
from Accesss. Thanks for the reply.
--
Brian Box


:

You need to tell EXCEL that the text should be handled as text.
Easiest
way
to do this is to export a calculated field where you've
prepended a
'
character in front of the string:

MyOutputField: "'" & [NameOfOriginalField]


--

Ken Snell
<MS ACCESS MVP>

I have compiled a text field in Access reports from numbers
(cricket
team
score such as 150-7 with appropriate spacing) but when this is
transferred
to
Access the field is converted to a number (date?). Should the
text
be
compiled at Query stage?