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!

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[Fionnuala]

I do not believe that will work, it causes a syntax error both in ADO and the query design window.

[Daria Dragomir]

You could also try leaving out the reference to the field entirely- insert (fld1,fld3) values (val1,val3)

[Daria Dragomir]

Yeah, you're right, forgot about that. Since it's a date field, try zero.

[Daria Dragomir]

(sigh) OK, we also had an issue with a DB2 ODBC driver where it had to be configured a certain way to pass dates to MS Access dbs. It could be something with the ODBC driver settings.

[Fionnuala]

You must have a from table with Access, that is not going to work.

[David-W-Fenton]

Replace the SELECT... with VALUES (Null, "foo")

[BIBD]

@Remou - I've tried it natively in MS Access 2007 - and it works. That was a straight copy and paste

[BIBD]

@David - Like wise, natively in MS Access 2007, values(null,"foo") doesn't work, and clearly it doesn't for the OP either, based upon their first try/example.

[HansUp]

You can INSERT a single-row SELECT without a FROM. If you wanted to UNION two SELECTs for the INSERT, that would be a different story.

[Fionnuala]

@CodeSlave Interesting, you are right on the select not needing a from table in this instance, however, I do not see why Values does not work for you. It is standard SQL and has worked on every version of Access in ADO and native since I can recall.

[David-W-Fenton]

I just tested this SQL INSERT INTO tblCustomer (LastName, Created) VALUES ("Fenton", Null); in Access (the Created field has a default value), and it worked exactly as expected. I did it in the QBE SQL view, not using any special mode (e.g., default SQL 89). As @Remou says, this has always been supported in Access/Jet/ACE.

[David-W-Fenton]

@CodeSlave: there was obviously something wonky with your instance of Access. This has always worked in a default Access install, and there's no need to use SQL 92 mode to make it work.

[Teekin]

I cannot leave it blank because it has a default value, so if I leave it, it won't be null.

[BIBD]

@David. What can I say? It was a vanilla install (all defaults), patched up to date, and I'd never swapped ANSI versions before. It failed the same way as the OP, and wouldn't let me insert using "values" at all. Flipping between 89 and 92 and back to 89 made it work. I would hypothesise that you, Remou and HansUp probably have flipped back and forth (or move to 92 permanently) on your installations before testing. The only way to tell is with a fresh installation; possibly on a VIRGIN workstation. I can't try it here right now, but I'll build something it on Tuesday/Wednesday to prove it out.

[BIBD]

I'll also note, when I built the insert query the first time in the query builder, it created it exactly as it was in my first example. After doing the 89-92-89 flip, it did it the second way. Anyone have a contact on MS's office development team. This is a neat one.

[Teekin]

I'm using the ODBC components in PHP. It shouldn't matter though, since I'm just passing SQL to the ODBC driver.

[David-W-Fenton]

I don't use SQL 92 except for testing things on SO! So, yes, my installation has been flipped, but support for SELECT-less inserts is not a SQL 92 issue in the first place. It's bog-standard SQL that was supported before SQL 92 mode even existed. I just tested it in A97 and it worked fine. My guess is that you have multiple versions of Access installed and ended up running in a version that had not completed the re-registration process after running one of the other versions.

[David-W-Fenton]

I don't work in PHP that often, but is there perhaps a PHP magic value ofr Null or something? That is, are you passing the word "Null" as a string in the SQL statement? It's a long shot...

[BIBD]

Sorry David, no dice. Vanilla install. But the fact that you have switched back and forth previously would be consistent with what I experienced. Like I said, I'll try it on a brand new machine this week.

[David-W-Fenton]

It's all a red herring as the OP isn't using Access at all, so our installations of Access don't have anything to do with his/her problem.

[David-W-Fenton]

I just tested on a different machine with a A2003 install that I know has never been flipped between SQL 89 and SQL 92 mode. The SELECT-less insert worked just fine. Trying the same thing on the same machine in A2010 (also never having SQL mode flipped) also worked. I don't know what the source of your problem is, but it isn't something inherent to Access or Jet/ACE.