Yes/No Check Boxes

[Guest]

Hello,

We have an Access database that acts as a front-end data entry system. The
data is then read into SAS for statistical analysis. We have a number of
fields in the Access tables that are defined as 'Yes/No' data type check
boxes. We are receiving complaints that the un-checked Check Boxes
(equivalent to 'No') are showing up i the SAS data set as blank (instead of
'0'). When I export an Access table to Excel, an un-checked Check Box is
read by Excel as 'FALSE'. Is there anything I need to do on the Access table
side to help the un-checked values show up as '0' instead of blank?

Thanks.

 

[Allen Browne]

Pat, yes/no fields can have 3 possible states:
- Yes (checked);
- No (unchecked);
- Null (neither checked nor unchecked.)

A yes/no field in an Access table does not permit the 3rd state, but it
still occurs. For example, an outer join query can easily produce Nulls in
yes/no queries. Since Access does not handle these properly, these queries
can cause Access to crash (shut down by Windows.)

If you are exporting queries, you may need to use Nz() around the field to
force the null to false. For example, you might replace MyYesNoField in the
Field row of the query design grid with this expression:
Nz([MyYesNoField], False)

In older versions of Access, the 3rd state was clearly identified. The check
boxed appeared greyed instead of checked. Unfortunately, Microsoft broke
this functionality, so Access 2003 on Windows XP does not visually
distinguish the 3 states properly, unless you uncheck the setting under:
Tools | Options | Forms/Reports | Use Windows Themed Controls.