Query Export Issue

[Craig]

Hello. This is the second time I've posted this. The answer I received
prior is not practical.

I'm using Access 97. I have a query used to export data to a .csv
file. The issue our users have is that Excel confuses some of the data and
converts the type incorrectly.

For example - one of the fields in the query export is called 'phase', and a
possible value in this field could be "5-5-1". However, When the user
exports the file and attempts to open it in Excel, Excel will convert this
into a date (05/05/01). I'd like to ensure the values in this (and other
similar) fields are exported
as text.

Is there a function to trap these values in my export query to ensure that
they are recognized exported as text values and not dates?

Thanks in advance

PS The answer to this question prior was that I should format the Excel
spreadsheet as text before I open the .csv file. Not practical for the user.
I want the value TO BE TEXT when it is exported from Access.

 

[Maurice]

Craig,

I assume it has to stay a .csv file? Otherwise you could export it as a text
file and import that in the excelsheet. Option two is to create a table based
on the query and set all the fields to text. Create an appedn query and run
the query appending the records. Now use this table as the source for the
..csv-file.

hth

 

[Craig]

Good point. It may not have to stay a .csv file. The only reason it's a
..csv is because the client specifically asked if it is possible to export
data to Excel. These people are not too data saavy and asking them to take
it one step further and import a text file may be pushing the limits!

I think your append solution is the ticket, though. Appreciate the help.

 

[John Spencer]

Another option is to use a query and append an apostrophe to the beginning
of the text when you export the field to the csv file.

In the query grid (qbe) it might look something like the following

Field: TheFieldName: "'" & [TheTableName].[TheFieldName]

The first part is of that is " ' " where I have added spaces to make
it clearer what is there.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Maurice]

Hi John,

When exporting it that way will the output in Excel show the apostrophs in
the output? I'm asking you this because i had to remove the apostrophs once
in an Exceljob and VBA didn't recognize the apostrophs as a character in the
cell. The only way to get rid of those was to export it as a textfile and
import the textfile into excel again. Seems like a big burden...
--
Maurice Ausum

Another option is to use a query and append an apostrophe to the beginning
of the text when you export the field to the csv file.

In the query grid (qbe) it might look something like the following

Field: TheFieldName: "'" & [TheTableName].[TheFieldName]

The first part is of that is " ' " where I have added spaces to make
it clearer what is there.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Craig]

Yes, I have given this a try. It does export the value as text. However,
the apostrophe does show up in Excel (as Maurice indicates). Although this
solution works, I'm not sure how the client will receive it.

It's interesting - this solution exports the value with an apostrophe
affixed as a prefix. But we all know that one way of converting a numeric or
date value to a text value in Excel is by simply placing a " ' " in front
of the value. However when you do this manually, the " ' " doesn't show
up. But when you export it as John suggests, it does.

Thanks for the suggestions.
--
Craig

Another option is to use a query and append an apostrophe to the beginning
of the text when you export the field to the csv file.

In the query grid (qbe) it might look something like the following

Field: TheFieldName: "'" & [TheTableName].[TheFieldName]

The first part is of that is " ' " where I have added spaces to make
it clearer what is there.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..