CODEWITHSUNDEEP
excelms-access
iDevlop
iDevlop

Reputation: 25272

Access to Excel: boolean is changed to numeric

I noticed that when transfering a query result from Access to Excel, whether by copy/paste or by using DoCmd.OutputTo, True/False values become -1 or 0 in Excel.
I could of course write a VBA proc to fill the Excel but I was wondering: is there any trick I could use to make the simple transfer work? In other words, which value should I assign to the query column to get a proper True/False in Excel ?
I also noticed that in Excel, True is NOT equal to -1. It's not just a matter of formatting, like in Access.

Tried so far without success:

Upvotes: 0

Views: 1116

Answers (1)

Gustav
Gustav

Reputation: 55921

In your query, you can use this expression to force a True/False string to Excel during export:

SpelledBoolean: Format([YourBooleanField], "True/False")

To return a 0 or 1, use Abs:

ZeroOneBoolean: Abs([YourBooleanField])

Upvotes: 1

Related Questions