Boolean Field Format?

[lameid]

I must confess I rarely use queries for reporting or use boolean fields. I am doing both a lot recently.

I am hoping there is an easy way to format a yes/no field to display yes/no (or anything more intuitive than 0/-1) in a query. Obviously I can change the data with IIF. Have I missed something?

 

[dhookom]

You should be able to simply set the Format property of the text box that displays the boolean field.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I just upgraded my query results to satisfactory.

I changed the table design.

The Display control was set to check box instead of Text box. This meant that data sheet views showed -1 or 0 if not a text box and the format options for the field in the query defaulted to a list for numbers as opposed to booleans. It would also export numbers to Excel as opposed to booleans. Now when I export to Excel it shows True or False. I would prefer Yes or No but I can live with T/F.

I experienced this using Access 2003.

I'm a big fan of search tags for later:

Checkbox display control for boolean field causes query results to be a number.

Boolean format options are numeric.

 

[dhookom]

A boolean field always stores 0 or -1 in an Access database. I always set the display format in controls on forms and reports. I rarely if ever set formats in tables and/or queries since my users never see tables or queries.

I wasn't aware this query was being exported to Excel. If this is the case, you may need to set the column in the query to:

NewColumn: IIf([yes/no field],"Yes","No")

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I am inclined to always format things on forms or reports too. However, there is not sufficient capacity in the project owner to develop proper requirements so there is adhoc analysis made in Excel made after Access does the heavy lifting. The tendency to do things in forms and reports and rare use of Yes/No fields explains how I never ran into the bug before.

I am hoping things will evolve into a proper reporting tool. However, there are generic reporting requirements where some data consumers do not have Access.

While I agree that access stores data as 0/-1, I disagree that Yes/No is equivalent to a byte, integer, Long, or double. I am saying that when the display control is set to a checkbox, the query result has the datatype to some numeric datatype.

Also, I am avoiding IIF in the query as I want the datatype to perist in any reports or subsequent queries I may run off the query. The Checkbox is what really through things off here. BUG BUG BUG