Make table query boolean format

[Erick]

I have a 'Make Table Query' where one of the fields I am trying to populate with Boolean (False) record, so I have:

Updatable:CBool(0)

However, when it makes the table, the format reverts back to integer. How can I make a table field with boolean type?

 

[Norie]

Have you tried using False?

 

[SydneyGeek]

Is it Integer, or is it just displaying numbers? In the Lookup tab of the field's Properties, you can pick Yes/No, True/False, -1/0 and you can also display as Textbox, Combo or Checkbox.

Denis

 

[Erick]

Have you tried using False?

I have tried that and still comes in the table as number.

Is it Integer, or is it just displaying numbers? In the Lookup tab of the field's Properties, you can pick Yes/No, True/False, -1/0 and you can also display as Textbox, Combo or Checkbox.

Denis

In the table created by the query, in design mode, when I look up the data type of the field, it is 'Number' rather than 'Yes/No'. I need the data to be boolean type because i need to import it into a 3rd party software program and it throws out an incompatibility error. The error goes away when I manually alter the data type to 'Yes/No' but I need for this to happen automatically through the make-table query.

 

[xenou]

1) What does the third party software actually require as input? Access interprets True as -1 and some systems use 1 - so this could be the problem, rather than Number/Boolean as such.

2) You can append to an existing table with the data types already there rather than make a table (this is actually a two step process - first, clear the table with a delete query, second, append the records).

3) You probably could also use a query to query the table you made - the query can format or convert data types as needed for output.

 

[Erick]

1) What does the third party software actually require as input? Access interprets True as -1 and some systems use 1 - so this could be the problem, rather than Number/Boolean as such.

2) You can append to an existing table with the data types already there rather than make a table (this is actually a two step process - first, clear the table with a delete query, second, append the records).

3) You probably could also use a query to query the table you made - the query can format or convert data types as needed for output.

Thanks Xenou for your reply.

I think the 3rd party software is looking for the data type to be 'Boolean'. It's weird because if I copy and paste the table into excel and import it, it works without any modification. But in access, unless I change the data type of the field to 'Yes/No' it says that the data type is incompatible. This is the reason why I think it is the data type that is the problem and not the actual value (-1, 1 or 'True').

I have 2 fields in the table that I am creating through 'Make-table' query that are suppose to be boolean type. 1 field is pre-set to false (all field record = false) and the other one to true.

Since I am running the make-table query through VB, just to get around this problem, I'm thinking of a 2-step process as well. First step is to create the table via SELECT....INTO and then step 2 change the data type via ALTER TABLE query.

I was just hoping I could do this somewhat cleaner by executing 1 SQL code.

 

[SydneyGeek]

Xenou's suggestion about deleting / appending is a good one. Once you have the correct datatypes you can modify your data without having to worry about compatibility.

If you want to know how to change datatypes on the fly, take a look at these links:
http://support.microsoft.com/kb/304321
http://www.everythingaccess.com/tutorials.asp?ID=DDL-query-code-examples
http://www.datawright.com.au/excel_resources/excel_access_and_ado_part_3.htm

Note that you will need to set a reference to

 

[Erick]

Thanks. I got it working through APPEND TABLE.

Thanks for the input.