Boolean Data Type Conversion 1

[akins4lyfe]

How do i convert the "Boolean" Datatype in a Make Table Query pls?.

I set up a make table query, based on a table which has one of its row data type as Yes/No.

When the Make Table query runs, i noticed the results inside the Yes/No fields appear as 0 or -1.

How do i convert this field within the criteria before running my query, so that i only have the check box and not 0 or -1.

Thanks

 

[dhookom]

A checkbox is a user interface format. There is nothing keeping you from using a checkbox on your forms or reports.

Duane
Hook'D on Access
MS Access MVP

 

[akins4lyfe]

Sorry if you misunderstood my question. I know what a checkbox control is, and how to use it on forms.

Here is my SQL for creating a new table, using the make table action query in access:

SELECT tblCustomer.MicroBusiness, tblCustomer.Rdate INTO tblRdate
FROM tblCustomer
WHERE (((tblCustomer.MicroBusiness)=True) AND ((tblCustomer.Rdate)="bgmr" And (tblCustomer.Rdate)="bgcd")) OR (((tblCustomer.Rdate)="mmrg"));

The MicroBusiness field has a Yes/No datatype.

The above query runs correctly, and produce the desired result, but once the new table is created, the Micro_Business row, which was set to TRUE, within the query criteria, generates results and inserts values of -1, instead of having a yes or no field.

How do i convert the MicroBusiness row into a yes/no fields, when running my query. So when the new table is created, i will have Boolean data type rows, not -1.

Thank you

 

[dhookom]

I ran a similar maketable query with a table containing a Yes/No field that displays as a check box. My "made" table retains the data type of Yes/No from the source table. It doesn't display a check box since this is a "Display Control" property in table design which is not set during the make table.

You would need code to add and set the property. An alternative might be to have the tblRdate already existing and append the records into it.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What may not be clear from what everyone is saying is that all boolean variables are stored as -1 or 0
-1 (true)
0 (false)

The table, query, and control properties allow you to display this in many ways. Also allows you to determine how the values are input

In table design there are two tabs. General and Lookup.
The top image shows general and you can choose the format
Yes/No
True/False
On/Off

(however the values behind the scenes are still just 0, -1)

The second tab shows you choices for the input
Checkbox
combobox
TextField

So instead of a checkbox the table could have a combo with the choices of On and Off. However, -1 and 0 are what is really stored.

If the datatype is actually changed then check if the field is now numeric instead of boolean.

 

[MajP]

BTW if you really want to do this from code this procedure will allow you to pass the name of the table, field, and the control type you want and it will alter the table.

Public Sub changeBooleanControlType(tblName As String, fieldName As String, controlType As Integer)
  Dim db As DAO.Database
  Dim tdf As TableDef
  Set db = CurrentDb
  Set tdf = db.TableDefs(tblName)
  If tdf.Fields(fieldName).Type = dbBoolean Then
    If controlType = acComboBox Or controlType = acCheckBox Or controlType = acTextBox Then
      tdf.Fields(fieldName).Properties("DisplayControl") = controlType
    End If
  End If
End Sub

example
changeBooleanControlType "tblOne","blnField", acCheckBox

that changes the field "blnField" in table "tblOne" to a checkbox.

 

[dhookom]

MajP,
Thanks for all the clarification. One point to keep in mind is the DisplayControl property does not exist in the new table until it is created. The code you provided will generate the error "Run-Time error '3270': Property not found."

There should be error handling that will create the property if it doesn't already exist. This requires the CreateProperty method with syntax like:

expression.CreateProperty(Name, Type, Value, DDL)

Duane
Hook'D on Access
MS Access MVP

 

[akins4lyfe]

Thank you all for your the input.

This is now resolved. I simply altered the table, which the "MakeTable" query is selected, adjust the property Look up option to "Check box", so when my query runs, i have the Yes/No data type retained, and of course the Yes/No field.

Thanks MajP

 

[dhookom]

MajP gets a star for a "Thank you". This both awards MajP for the effort and identifies the thread as having been resolved.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
Thanks I was just being lazy. But yes normally whenever working with adding, deleteing, or modifying properties of the "properties collection" one should always check first if the property exists (or at least trap the error).