Ken Snell (MVP)

[Garry]

Hi all, have had trouble myself with this for a long while.

Have done as you suggested and the full field is exported but the '
characters appears in the excel worksheet and also prints out to paper.

What am I doing wrong

cheers, Garry

 

[Ken Snell \(MVP\)]

Can you provide a few more details about your report's Recordsource query
(SQL statement) and how you're exporting the data (are you also using the
Export to EXCEL option from the report, for example)? Are you exporting to a
new EXCEL file or to an existing file?

 

[Garry]

Hi, I am exporting to a new xls file (Microsoft 97-2002) from this
recordsource query
SELECT "'" & [URN] AS Ref FROM [Client Data];

cheers

 

[Ken Snell \(MVP\)]

If the URN field is a Text datatype in the Client Data table, then there
should be no need to put a ' character in front of the field in the query.
My testing shows that a text field is exported to EXCEL as a text field,
even if the field contains only number values. Putting the ' character in
front of a text field will give you two ' characters at the beginning of the
value (click into the EXCEL cell and you'll see two ' characters at the
beginning of the value, not one) -- so the first one is "hidden" and you see
the second one in the cell when viewing the spreadsheet.

You should use the ' character as a prepended character only if the field
datatype is a numeric one.

--

Ken Snell
<MS ACCESS MVP>

Hi, I am exporting to a new xls file (Microsoft 97-2002) from this
recordsource query
SELECT "'" & [URN] AS Ref FROM [Client Data];

cheers

Can you provide a few more details about your report's Recordsource query
(SQL statement) and how you're exporting the data (are you also using the
Export to EXCEL option from the report, for example)? Are you exporting
to a new EXCEL file or to an existing file?