Access SQL boolean data type needed 2

[merlynsdad]

I need a column with data type as a boolean. I had thought the correct data type was "yes/no", but apparently not, as vba won't make the table that way. Here's the SQL:

strSQL = "CREATE TABLE tblTemp_Event (location varchar(255), event_type integer, date_change yes/no);"
Debug.Print strSQL
db.Execute strSQL

What is the correct data type syntax for a boolean?

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

Why not just create an integer column? I have answered several questions here where users had a yes/no column and later determined they needed to store values other than yes/no. An integer column does everything a yes/no column does and much more.

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

I suppose I could use an integer, but we're just raising a flag to show that something else happened, so a yes/no would be more appropriate. Is there a boolean data type that would work here?

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

If you will only ever need just yes or no values then use YESNO rather than yes/no.

strSQL = "CREATE TABLE tblTemp_Event (location varchar(255), event_type integer, date_change yesno);"

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

I stay away from the Access Yes/No field because if you ever upsize the table to a SQL Server the Access boolean fields are the source of bugs on some versions of SQL Server, which drove me nuts once upon a time. Access check boxes have some sort of issue using SS 2000-05 bit fields IIRC, which is what an Access YesNo field is converted to in SS. The way to avoid the problem is to declare all Access boolean fields as integer, with a default of 0, which when upsized becomes a SS smallint and then the checkboxes will work correctly.