| One annoyance involved in bringing data from a database to the worksheet is due to Boolean fields. Booleans are also termed True/False fields, and Access often refers to them as Yes/No fields. Boolean fields take one of just two possible values: True and False. Difficulties arise when you return their values to the worksheet because databases don't necessarily store them as TRUE or FALSE, the values you normally see on an Excel worksheet. You need to account for this, either in the database or in Excel. And check boxes, which usually result in either a True value (checked) or a False value (unchecked) can have a third value, Null (shaded). Returning Boolean Values to the Worksheet Suppose that your database contains a table named Patient_Restraints, and that the field Medical_Reason is defined as Boolean. Figure 5.29 shows how that field might look with the table in Datasheet view. Figure 5.29. Access displays Boolean fields as check boxes in Datasheet view. 
Figure 5.30 shows how Medical_Reason appears on an Excel worksheet, depending on how you put it there: If you select the field in the Access datasheet, copy it, and then switch to Excel and paste it to a worksheet, you'll see TRUE and FALSE values such as those shown in cells A1:A11 of Figure 5.30. If you bring the data into the worksheet by means of Import External Data, the TRUE values appear as 1's and the FALSE values appear as 0's. This is shown in cells C1:C11 of Figure 5.30.
Figure 5.30. Pasting an Access field into a worksheet provides the field name as a header and sets the header's fill to gray. 
If you want to deal with something other than the 1's and 0's in an external data range, consider refreshing the data using a VBA procedure such as this Open event handler: Private Sub Workbook_Open() Application.Goto Reference:="Query_from_MS_Access_Database" Selection.QueryTable.Refresh BackgroundQuery:=False Application.Goto Reference:="Query_from_MS_Access_Database" With Selection .Replace What:="0", Replacement:="FALSE", LookAt:=xlWhole .Replace What:="1", Replacement:="TRUE", LookAt:=xlWhole End With End Sub
This code first selects the existing query range, and then refreshes its data. It then reselects the query range in case the number of records changed due to the refresh. Finally, it replaces 0's with FALSE and 1's with TRUE. It replaces a 0 or 1 only if it is the cell's whole value, to avoid, for example, replacing a header cell's value of Medical_Reason1 with Medical_ReasonTRUE. Managing Data from Check Boxes When an Access form's check boxes are associated with Boolean fields, no new difficulties arise. But sometimes you need to provide for not just TRUE and FALSE values, but a Null value as well. This Null value might mean something such as Not Applicable. Suppose that in the Patient_Restraints table, the Medical_Reason field needs to take on three values: TRUE when a patient was restrained for a medical reason, FALSE when there was some other reason, and Null when a restraint was not used. If the Medical_Reason field is defined as Boolean, it can't distinguish between FALSE and Null; in both cases, a record's field is not checked. But if you define the Medical_Reason field as an Integer, it can take on many more values. And that opens the possibility of associating it with a check box on a data form that in Access has a TripleState property. With TripleState turned on, the form's check box can be checked (TRUE), unchecked (FALSE), or shaded (some other meaning, typically Not Applicable). Figure 5.31 shows how that data form might appear on the screen. Figure 5.31. The shaded check box indicates a Null value. 
Whether or not you set the form's check box to take on one of three values, if the underlying field is numeric then TRUE is stored as 1 (instead of 1), whereas FALSE is still stored as 0. If the form's check box is set for TripleState, the field itself stores a Null as the value when the check box itself is shaded. Figure 5.32 shows how external data ranges appear when a field is defined as numeric and its values are determined by a check box on a data form. Cells A1:A11 show 10 values. The five 1's mean that the form's check box was checked for those records. The five 0's mean that the form's check box was not checked. Figure 5.32. To help distinguish between a Null value and no record, select the range's Include Row Numbers property. 
Cells D1:D11 also show 10 values. The 1's mean that the check box was checked and the 0's mean that it was unchecked. The blank cells in rows 8 through 11 mean that the check box remained shaded. If you replace 0's with FALSE and 1's with TRUE in the external data range, you should avoid replacing row numbers that are shown in column C. Therefore, alter the code shown previously to replace outside the column of row numbers: Sub Workbook_Open() Dim ExtRange As Range Dim NCols As Integer, NRows As Integer Set ExtRange = Worksheets("Sheet1") _ .Names("Query_from_MS_Access_Database").RefersToRange NCols = ExtRange.Columns.Count - 1 NRows = ExtRange.Rows.Count Application.Goto Reference:="Query_from_MS_Access_Database" Selection.QueryTable.Refresh BackgroundQuery:=False ExtRange.Offset(0, 1).Resize(NRows, NCols).Select With Selection .Replace What:="0", Replacement:="FALSE", LookAt:=xlWhole .Replace What:="-1", Replacement:="TRUE", LookAt:=xlWhole .Replace What:="", Replacement:="#N/A", LookAt:=xlWhole End With End Sub
This code counts the number of columns and rows in the external data range, and then selects the range of cells that's offset from that range by one column, with the same number of rows and one fewer column. In that selected range, it replaces 1's and 0's as before, and additionally replaces blank cells with the #N/A error value. | 