ACCESS = "NO" BUT EXCEL = "0"

[pauld]

Sometimes when I export a query that contains a text field to Excel, the word
"NO" in the Access database text field shows up as a "0" (zero) in the
exported file.

The Access text field has more than a yes/no possibility so that's why I use
a text field

Is there some setting or format that I should be specifying to make sure the
NO stays as a NO.

Thanks.

 

[Graham Mandeno]

Hi Paul

I think this is Access and/or Excel trying to "help"

You could try prefixing a single quote to the value in the text field. Use
a query to export (if you aren't already), not a direct export from the
table.

Then, instead of adding the text field as a column in the query, add a
calculated field like this:

FieldName: "'" & [TableName].[FieldName]

The "'" is a single quote enclosed in double quotes - " ' " without the
spaces.

I assume you are using TransferSpreadsheet. If this still doesn't work, I
can show you how to write some of your own code to write directly to the
Excel worksheet. Post back if you're still having trouble.

 

[fredg]

Sometimes when I export a query that contains a text field to Excel, the word
"NO" in the Access database text field shows up as a "0" (zero) in the
exported file.

The Access text field has more than a yes/no possibility so that's why I use
a text field

Is there some setting or format that I should be specifying to make sure the
NO stays as a NO.

Thanks.

I think you would be better served by posting this question in an
Excel newsgroup.