CBOOL does not affect return db Data Type in query

[GK in the UK]

I've hit a snag whilst following Allen Browne's recommendation to not use Yes/No fields in tables. Instead, he says use Integer data type and Type 106 Check Box.
I have a pseudo "boolean" type in a table defined as number, integer and with field type 106.

The issue I have is that when I run a query on the table, the data type always returns as Integer (type 3, dbInteger). Even when I convert the table field with CBOOL in the query, it still returns type dbInteger.

I want to return the data type as dbBoolean. Right now, I have no native Integer types for use as numbers in my DB so I can sort of accommodate it but it's less than ideal. Is there a way of determining or forcing the query to return some flag so that when I convert the field with CBOOL I can identify it?

 

[The_Doc_Man]

The problem is that for native Access, Boolean isn't really Boolean. It is a typecast (alternative interpretation) of native type BYTE INTEGER. Just like DATE is a typecast of DOUBLE and there are a couple of others. So when you return a query with a CBool() function as one of the items, its base type is still integer.

I guess I have to ask the obvious... why does it matter, since Access Boolean is not a single-bit value either? Or is this something you are trying to convert for some variant of SQL that actually DOES have a single-bit Boolean?

 

[GK in the UK]

I'm building a generic query builder where I feed it a base query, and for each query field I populate some combo boxes according to what can be done with the field. So for a numeric type, the combo shows options Equals, Between, Greater than, etc etc. The idea is that for Boolean types different options are offered.

As it stands any pseudo-boolean field that I've defined per Allen Browne, is going to appear as Integer, and my combo is going to offer nonsensical options.

In testing I set the combo for native integer type as though it was a boolean field, and it works, but it means if I pass in a query with a 'proper' integer field it's going to break.

 

[jdraw]

I'm not sure if this is relevant to your generic query builder, but you can use format options for display for info:

I have a table named data_dictionary.
This the table structure

strSQL_Create = "CREATE TABLE data_dictionary" & _
              "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255)," & _
              "field_name varchar(250),field_description varchar(255)," & _
              "ordinal_position NUMBER, data_type varchar(18)," & _
              "length varchar(5), default varchar(30), Reqd bit);"

<<--Note Reqd is bit


If querying against the table, the Reqd field is displayed as Yes/No with the format statement, or -1/0 without.

SELECT data_dictionary.table_name
, data_dictionary.field_name
, format(data_dictionary.Reqd,"yes/no") as Required
FROM data_dictionary
WHERE (((data_dictionary.field_name) Like "employee*"));

Query61

table_name	field_name	Required
PayRollTime	EmployeeId	Yes
PayRollTime	EmployeeName	No
SampleEmpData	employeeID	No
tblEmployee	EmployeeId	Yes
tblEmployees	employee number	No

You could also have True/False or On/Off