'Create Table' and Yes/No Field

[raskew]

Hi-

Attempting to include a Yes/No (boolean) field using a 'Create Table'

statement. Have yet to find working syntex. Example:


This works:
strSQL = "CREATE TABLE " & tName & "(Let TEXT (1));"

This doesn't work:
strSQL = "CREATE TABLE " & tName & "(Let TEXT (1), Selected YES/NO);"

Nor does this:
strSQL = "CREATE TABLE " & tName & "(Let TEXT (1), Selected BOOLEAN);"

Have searched extensively and have yet to find any example. In fact, some

listings of Data Types don't include Yes/No (Boolean). Am beginning to

wonder if it's even possible.


Any thoughts?

Thanks - Bob

 

[pbaldy]

Try BIT as the data type

 

[datAdrenaline]

Hey Bob ...

The MSDN article titled Comparison of Data Types is quite useful in these circumstances!! ...

The same info in the access help too ... I think I searched for "DataTypes" and this info was the eighth item in the list (A2003 SP2) ... but ... amazingly enough 'BOOLEAN' is supposed to work, but doen't seem to ... I always used BIT (like Paul suggested) or LOGICAL ...

 

[raskew]

Paul & Brent -

You are both princes (not to be confused with princess)!

strSQL = "CREATE TABLE " & tName & "(Let TEXT (1), Selected BIT);"

...works perfectly! I'm still confused as to what happened to YES/NO and BOOLEAN. Oh well, if it works--go with it.

If you've got a moment--to see where this is going--click on:
http://www.access-programmers.co.uk/forums/showthread.php?t=152493

I'd sure welcome any thoughts.

Best wishes - Bob

 

[pbaldy]

I think YESNO will also work (the "/" would cause a problem), but not sure why BOOLEAN doesn't, since the others are just synonyms for it according to Brent's reference.

 

[Banana]

This isn't my forte, but I wonder if it could have to do anything with the differences I noted between a Yes/No field type and VBA's boolean data type:

Yes/No field type is 1 bit, and VBA boolean is 2 bytes.

 

[raskew]

Paul -

You're absolutely correct (as usual)--

strSQL = "CREATE TABLE " & tName & "(Let TEXT (1), Selected YESNO);"

...works as advertised.

Still mystifed why this method, or Brent's, was so hard to track down.

Appreciate your quick responses.

Best Wishes - Bob

 

[raskew]

Banana -

I get totally lost reading thru that thread.

Think we may be missing something here, but not sure what it is.

Appreciate your response.

Best Wishes - Bob

 

[Banana]

Sorry, I have a tendency to get philosophical.

Basically, the point is that Yes/No field is defined as 1 bit long data type, while VBA's boolean is 2 bytes long.

This means we've been doing implicit typecast all the along whenever we were dealing with Yes/No fields because no other data types are smaller than a byte (e.g. 8 bits). In your case of Boolean failing made me wonder if this was a case of where they forgot to implicitly typecast the boolean into bit, and thus the create table query failed because they didn't have a Boolean data type, and didn't typecast into a bit as required for a Yes/No field.

Now, to be honest, I don't know enough about DDL in Access (please observe how fricking lazy this user is. ) to test for sure, but maybe someone can confirm this or not.

Did that help?

 

[pbaldy]

Bob, if it was easy, anybody could do it and we wouldn't be making the big bucks answering questions here!

Actually I hadn't needed that before, and looked in the JetSQL help file under data types.

 

[datAdrenaline]

Hello Bannana ...

The difference in bytes consumed has nothing to do with it, otherwise the BYTE would be messed up too since a VBA Byte consumes 2 bytes of memory (I know that does not make sense, but that is what I have found), but is stored in a table with just 1 byte (although I have not confirmed that ... yet) .... so ... its just keywords, syntax and interpretation by the SQL interpreter. So, apparently the BOOLEAN keyword has been dropped or was errantly indicated in the first place, it would not be the first time that help files were incorrect!

Also, I have been watching that other thread, and I will post to it later tonight if time allows. Some cool stuff, definately! ...

 

[Banana]

Back up a bit.

You're telling me that a Byte data type consumes 2 bytes?

Wow. That's even crazier! Now my curiousity is quite very piqued.

As for differences in sizes, I already noted that there is obviously a lot of implicit typecasting going in background, else everyone would be raising holy hell about why they couldn't query their boolean column and put it in a boolean data type and just a million other ways. But when someone's doing the work, there's a chance that they may forgot to patch up that one tiny hole, hence my speculation. I could be dead wrong, but as I said, I'm very very curious about the background now.

 

[datAdrenaline]

>> You're telling me that a Byte data type consumes 2 bytes? <<

Yep ... (The VBA "Byte" datatype)

 

[Brianwarnock]

Hello Bannana ...

So, apparently the BOOLEAN keyword has been dropped or was errantly indicated in the first place, it would not be the first time that help files were incorrect!
QUOTE]

In the "old old days" IBM frequently issued documentation corrections, after all it was easier than correcting the software.

Brian

 

[georgedwilkinson]

In the "old old days" IBM frequently issued documentation corrections, after all it was easier than correcting the software.

Ah, those were the good old days. Good luck finding any documentation correction (errata) with today's software.

 

[Banana]

Ah, those were the good old days. Good luck finding any documentation correction (errata) with today's software.

Hush. Don't speak blasphemy in front of those marketing guys.


I'm using a library, and it has 'foo' and 'bar' all over the places as a parameter, argument or variables! Loads of fun!