Exporting Query to Excel and Apostrophes

[Shohoku79]

Hello:

I have written a query that pulls data from two tables and into 4 fields.

Date (Date)
Number (Autonumber)
Name (Text)
Amount (Currency)

I gave the user the option to export to MS Excel with the file name/location
of his choosing. When I opened the excel file, it looks like the data
exported correctly, with the exception of the Name field, it had an extra '
(Apostrophe) before the names in the formula bar, but not in the sheets.
e.g.
'John Smith (formula bar) / John Smith (worksheet)

From research it looks like Access/Excel does this to all fields to treat it
as text. I would like to know if there is a way to remove the apostrophes
from the fields from the Access side so when the user opens the file, the
apostrophes are already removed from the formula bars on all the text fields.

Thank you

 

[gllincoln]

Hi,

Access isn't doing that - Excel puts that leading tick in there for text
columns. It's a visible marker that says this is text / string data versus
numeric. Nothing on the Access side is going to remove that tick. You might
try changing the field type on the Access side to Memo or dbChar or
something that can contain alpha characters but won't necesarily trigger the
same response on the Excel side as the type 10/text field does.

You might be better off educating your users as to the benefits/significance
of this leading quote - that this tells you that the information is
string/text content and serves as a reminder that you shouldn't be using any
numeric-based formulas on it, even if a particular cell 'looks like a
number.'

Hope this helps...
Gordon

 

[gllincoln]

Hi,

There is one way - sometimes if you format the entire column as numeric -
place the query data on top of that already formatted column - the old
formatting will sometimes be sticky and be retained.

Gordon