Menu
I am trying to add a Boolean column to a table in ms-access using SQL. I am using JET, here are the the SQL queries I have tried.
Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN DEFAULT FALSE"
Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN"
The error I am getting is 'Syntax error in field definition'
Thanks for your help
EDIT:
I would now like to make the default null rather than false. I have tried default null and this still gives me false, can anyone help with this?
RESULT:
An ms-access database can only take true and false and not null. Therefore I have decided to use and integer instead.
473
In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1. This will provide the boolean nature for a data type.
ALTER TABLE table_name ALTER COLUMN col_name SET NOT NULL; Or you can put them all together in a single statement: ALTER TABLE table_name ADD COLUMN “col_name” BOOLEAN DEFAULT FALSE; This way it might take longer if the operation is huge.
There is boolean data type in SQL Server. Its values can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).
You can update boolean value using UPDATE command. If you use the BOOLEAN data type, MySQL internally convert it into tinyint(1). It can takes true or false literal in which true indicates 1 to tinyint(1) and false indicates 0 to tinyint(1).
The equivalent SQL type of a Yes/No column is BIT
ALTER TABLE tabDatafiveMinutely
ADD COLUMN CON0001 BIT DEFAULT 0 NOT NULL
Microsoft's documentation says
Note
The DEFAULT statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.
As @Pere points out, Jet Engine (Access' query engine) does not apply the DEFAULT value to existing rows. You must run an UPDATE statement after altering the table.
UPDATE tabDatafiveMinutely SET CON0001 = 0 WHERE CON0001 IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
