MS Access: How does one insert NULL into DateTime field

Question

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!

Answer 1

Try just leaving it blank

(values fld1,,fld2)

Answer 2

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.

Answer 3

What are the libraries, you are using in order to talk to ODBC?
Could it be a problem with the syntax for null values, the way library interprets it?

See, if this page helps.
Note: I have not worked with PHP.

Answer 4

I tested with Access 2007 (Office Ultimate 2007) and SP2. Here is the DDL for the table I created:

Public Sub CreateTableDateTimeTest()
    Dim strSql As String
    strSql = "CREATE TABLE table_name (" & vbNewLine & _
        vbTab & "id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY," & vbNewLine & _
        vbTab & "datetime_field DATETIME DEFAULT Date()," & vbNewLine & _
        vbTab & "text_field TEXT(255) NOT NULL);"
    CurrentProject.Connection.Execute strSql
End Sub

And here is my INSERT statement:

Public Sub InsertNullDate()
    Dim strSql As String
    strSql = "INSERT INTO table_name (datetime_field, text_field)" & vbNewLine & _
        "VALUES (Null, '" & CStr(Now()) & "');"
    ' test INSERT with both DAO and ADO '
    CurrentDb.Execute strSql, dbFailOnError
    CurrentProject.Connection.Execute strSql
End Sub

The INSERT succeeded when executed by both DAO and ADO. I ran the same test with both MDB and ACCDB database formats. In every case the INSERT was successful.

I'm at a loss to explain why the OP's INSERT fails.

Answer 5

I don't know what the hell I did, but inserting Null works now.

Thanks to all who put effort into this! Access works in mysterious ways!

Answer 6

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