Q1: How to create a boolean table field in code?

[Jeff Conrad]

Hi,

Using Access 97 at the moment, but this project will also be compatible for 2000+.

I've completed making another Access Add-In and everything works fine, but there are two tiny issues
I'd like a little feedback on. Second issue will be in another thread.

I'm creating a table in code, but I cannot seem to create a Boolean field using the "Create Table"
statement. My workaround is to make all the fields except that one on the first pass. I then use a
second function to create a TableDef on the new table and use a CreateField statement to make the
Boolean field. This works fine, but what am I missing about using the Create Table statement?

As a test try this code from the immediate window:

Public Function funcTest()

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "CREATE TABLE tblTest " _
& "(FirstField TEXT, SecondField dbBoolean);"

Set db = Nothing

End Function

I get a syntax error. Instead of dbBoolean I've also tried:
Boolean, Yes/No, True/False, -1/0, Up/Down, etc, but nothing seems to work.

Is it not possible to make a Boolean field using the Create Table statement?

Thanks for any help,

 

[Dirk Goldgar]

Hi,

Using Access 97 at the moment, but this project will also be compatible for 2000+.

I've completed making another Access Add-In and everything works fine, but there are two tiny issues
I'd like a little feedback on. Second issue will be in another thread.

I'm creating a table in code, but I cannot seem to create a Boolean field using the "Create Table"
statement. My workaround is to make all the fields except that one on the first pass. I then use a
second function to create a TableDef on the new table and use a

CreateField statement to make the

Boolean field. This works fine, but what am I missing about using the Create Table statement?

As a test try this code from the immediate window:

Public Function funcTest()

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "CREATE TABLE tblTest " _
& "(FirstField TEXT, SecondField dbBoolean);"

Set db = Nothing

End Function

I get a syntax error. Instead of dbBoolean I've also tried:
Boolean, Yes/No, True/False, -1/0, Up/Down, etc, but nothing seems to work.

Is it not possible to make a Boolean field using the Create Table

statement?

Try any of these:

SecondField BIT

SecondField BOOLEAN

SecondField YESNO

SecondField LOGICAL

 

[Jeff Conrad]

Hi Yoda!
Comments below...

CreateField statement to make the


Try any of these:

SecondField BIT

Ahh haa! that one worked!

SecondField BOOLEAN

Nope, syntax error.
I'm still perplexed why this and dbBoolean do not work.

SecondField YESNO

This one worked as well.
I should have tried dropping the / symbol in my tests.

SecondField LOGICAL

This one also works.
Three out of four, not bad.

It now works just the way I intended and I was able to drop the extra function.
Sweet!

Many thanks Master!
(head bowed)

 

[Dirk Goldgar]

Nope, syntax error.
I'm still perplexed why this and dbBoolean do not work.

Hmm, I'm on vacation and don't have Access 97 installed on this laptop. The
help file for Jet 4.0 SQL -- topics "SQL Data Types" and "Equivalent ANSI
SQL Data Types" -- tells me that BOOLEAN should work, but maybe that synonym
for the BIT field type was not present in Jet 3.5.

I wouldn't expect dbBoolean to work. That's not a SQL field type; it's the
name of a DAO constant, and is only meaningful when used with DAO objects.

 

[Jeff Conrad]

Hi Yoda,

Hmm, I'm on vacation and don't have Access 97 installed on this laptop.

Humm......"relaxing" I see.
;-)

I guess I'm not one to talk.
I just got back from a vacation and I made this new Add-In while on vacation!


I was hoping to bump into some of the Canadian MVPs while visiting, but darn didn't see any.

The help file for Jet 4.0 SQL -- topics "SQL Data Types" and "Equivalent ANSI
SQL Data Types" -- tells me that BOOLEAN should work, but maybe that synonym
for the BIT field type was not present in Jet 3.5.

After testing everything (before seeing your post) I by coincidence looked up Bit in the help file
and it took me to the very topic you just discussed! It does list Boolean as a synonym. So I just
don't understand why it does not work with Boolean. Oh well, I have it working now.

I wouldn't expect dbBoolean to work. That's not a SQL field type; it's the
name of a DAO constant, and is only meaningful when used with DAO objects.

"Ahhh.....I see," said the Padawan.

Thanks again Yoda!
Have fun on your vacation and drop in if you are ever in Oregon!

 

[david epsom dot com dot au]

BOOLEAN doesn't seem work in A97 or A2.0 either
-- in spite of being in the help for both versions.

I always had this theory that it was disabled because
a BOOLEAN field was an INT field in some earlier
version, but either some one told me that or it was
a total fantasy, because I never used version 1.0
to know anything about it myself.

(Testing an SQL parameter clause.)

(david)