A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 10th, 2004, 08:27 PM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default Q1: How to create a boolean table field in code?

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,

--
Jeff Conrad
Access Junkie
Bend, Oregon
*282 Days Left*


  #2  
Old August 10th, 2004, 10:34 PM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default How to create a boolean table field in code?

"Jeff Conrad" wrote in message
...
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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)



  #3  
Old August 11th, 2004, 01:57 AM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default How to create a boolean table field in code?

"Dirk Goldgar" wrote:

Hi Yoda!
Comments below...

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


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)

--
Jeff Conrad
Access Junkie
Bend, Oregon
*282 Days Left*


  #4  
Old August 11th, 2004, 04:23 AM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default How to create a boolean table field in code?

"Jeff Conrad" wrote in message
...

SecondField BOOLEAN


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)



  #5  
Old August 11th, 2004, 05:21 AM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default How to create a boolean table field in code?

"Dirk Goldgar" wrote:

Hi Yoda,

SecondField BOOLEAN

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.


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.
g

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!

--
Jeff Conrad
Access Junkie
Bend, Oregon
*282 Days Left*


  #6  
Old August 11th, 2004, 08:22 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default How to create a boolean table field in code?

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)


"Dirk Goldgar" wrote in message
...
"Jeff Conrad" wrote in message
...

SecondField BOOLEAN


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Repost: Calculation problem. Someone help! Victor Running & Setting Up Queries 13 August 6th, 2004 05:21 PM
Code to change a table definition Tim General Discussion 3 June 30th, 2004 02:07 AM
I can see the data but... David F-B General Discussion 3 June 24th, 2004 06:15 AM
Create table with field names based on the value of a field in another table. [email protected] Database Design 5 May 24th, 2004 02:32 PM
Make a field lookup dependent on the value in another field of a record? Susan A Database Design 8 May 22nd, 2004 09:10 PM


All times are GMT +1. The time now is 04:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.