Reputation: 13279

MS Access: How does one insert NULL into DateTime field

I have an MS Access database (intolerably enough), and communicating with it through PHP (ODBC).

There is a DateTime field that I have to include in my INSERT statement. This field is NOT defined as "Required" in Access, meaning that it is indeed NULL-able, and in fact some of the rows in the Access database are already NULL.

The problem I'm having is simple: How to insert NULL through SQL? All the results I've found online have addressed it from something like Visual Basic or C#, whereas I'm using SQL through ODBC in PHP.

I have already tried the following:

INSERT INTO table_name (datetime_field) VALUES (NULL)
INSERT INTO table_name (datetime_field) VALUES (#NULL#)
INSERT INTO table_name (datetime_field) VALUES ('NULL')
INSERT INTO table_name (datetime_field) VALUES ('#NULL#')
INSERT INTO table_name (datetime_field) VALUES ('')

(There's about 30 other columns in my query.)

The exact error I get is 'Data type mismatch in criteria expression' when I try '' or NULL. The others return a parse error (understandably).

Please note that I have to include the field in the INSERT statement. The field has a default value, but in many cases the original data that I'm transporting has a NULL that must also be a NULL in the target database.

Thanks in advance!

Upvotes: 9

Answers (7)

Nate Hammond

Reputation: 56

I think it's case sensitive. I was using NULL and failing. Then I used Null. all good

Upvotes: 0

mjb

Reputation: 1

I have this type of error and Try this.

INSERT INTO table_name (datetime_field) VALUES (DBNull.value)

It works fine for me.

Upvotes: 0

ramesh kumar

Reputation: 91

INSERT INTO table_name (datetime_field) VALUES (DbNull.Value)

Upvotes: 1

masfenix

Reputation: 7996

I know you've already figured this out but there is also dbNull

Upvotes: 1

BIBD

Reputation: 15384

Try the following. It works for me:

INSERT INTO sometable ( somedate, somethingelse )
SELECT Null AS Expr1, "foo" AS Expr2;

Basically, you are wrapping the null in the select query and letting SQL figure out how to represent it to the insert.

-- EDIT --

This SHOULD also work:

INSERT INTO sometable ( somedate, somethingelse )
values (Null , "foo");

But for some reason it doesn't with my default install.

On I hunch, I switched my DB from ANSI-89 to ANSI-92, and the VALUES method started working. I switched it back to ANSI-89, and it still works. Not only that, on ANY new database I create, it now also works. Weird... something in the installation must be getting changed, (and sticking) by the switching back and forth that's not just ANSI-89/92. This seems to be why we were getting different results.

You can switch the database ocwe by going to Office Logo->Access Options->OBJECT DESIGNERS->QUERY DESIGN. Change SQL Server Compatible Syntax (ANSI 92) - and checking "This database".

Ok, very odd.

Upvotes: 4