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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |