Changing True/False to Yes/No in Excel

[PaultheS]

Just a simple question here.

Right now, when I CopyFromRecordset some data to an Excel file, it puts it in true/false format. Is there some way to change it to Yes/No on the Excel file? I don't care whether it changes the recordset or the format of the range, either one is fine in this instance (as the range is known).

 

[macropod]

Hi Paul,

Presumably, the underlying data are '1' for True and '0' for false. In that case, a custom number format ("Yes";;"No") could be used to display these same values as Yes/No.

Cheers

 

[TonyJollans]

Hi macropod,

I thought of something similar but it doesn't work for me - True and False don't get coerced into numbers in order for the formatting to take place - am I missing a trick?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

In the SQL code of the recordset you may try to play with the IIf function:
SELECT ..., IIf([your field]=True,'Yes','No') As myYesNO, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Hi Tony,

The solution really depends on how the data are represented in Excel. Obviously, if the data come across as 'True' and 'False', you'd have to replace it.

The solution I suggested works if the 'True' and 'False' conditions are represented as 1 and 0, respectively.

If the 'True' and 'False' conditions are represented as binary values, I think you'll find that squaring them will convert them to 1s and 0s. My solution would then work on the squared result. This could be done via Paste Special|Values|Multiply, or via a formula that squares the values in the source cells (eg =A1^2).

Cheers

 

[TonyJollans]

Hi PaultheS,

I presume that these are binary values in Access set to display as Yes/No and you would like the same in Excel.

By default you have a boolean value, macropod's solution requires numbers, PHV's solution presents text. I haven't tried this but what about combining them?

In your SQL, use [blue][tt]IIF([YourField]=True,1,0) As myYesNo[/tt][/blue], or perhaps just [blue][tt][YourField]^2) As myYesNo[/tt][/blue] and then format as per macropod's suggestion. That way you'ld see Yes/No but have values which would coerce into boolean in formulae.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PaultheS]

Hi everyone,

Thanks for your help. I ended up going with PHV's solution. This is what I ended up putting in the SQL statement:

IIf([field]=True,'YES','NO')

Worked like a charm.