Inserting null values into date fields?

The Problem (Q-score 7, ranked #65th of 67 in the Access VBA archive)

The scenario as originally posted in 2012

I have a FormView where I pull data from one table (MS Access), and then insert it (plus more data) into another table. I’m having issues with the dates.

The first table has two date fields: date_submitted and date_updated. In some records, date_updated is blank. This causes me to get a data mismatch error when attempting to insert into the second table.

It might be because I’m databinding the date_updated field from the first table into a HiddenField on the FormView. It then takes the value from the HiddenField and attempts to insert it into the second table:

Dim hfDateRequestUpdated As HiddenField = FormView1.FindControl("hfDateRequestUpdated")
myDateRequestUpdated = hfDateRequestUpdated.Value
'... It then attempts to insert myDateRequestUpdated into the database.

It works when there is a value there, but apparently you can’t insert nothing into a date/time field in Access. I suppose I could make a second insert statement that does not insert into date_updated (to use when there is no value indate_updated), but is that the only way to do it? Seems like there should be an easier/less redundant way.

EDIT:

Okay. So I’ve tried inserting SqlDateTime.Null, Nothing, and DBNull.Value. SqlDateTime.Null results in the value 1/1/1900 being inserted into the database. “Nothing” causes it to insert 1/1/2001. And if I try to use DBNull.Value, it tells me that it cannot be converted to a string, so maybe I didn’t do something quite right there. At any rate, I was hoping that if there was nothing to insert that the field in Access would remain blank, but it seems that it has to fill it with something…

EDIT:

I got DBNull.Value to work, and it does insert a completely blank value. So this is my final working code:

Dim hfDateRequestUpdated As HiddenField = FormView1.FindControl("hfDateRequestUpdated")
Dim myDateRequestUpdated = Nothing

If hfDateRequestUpdated.Value = Nothing Then
    myDateRequestUpdated = DBNull.Value
Else
    myDateRequestUpdated = DateTime.Parse(hfDateRequestUpdated.Value)
End If

Thanks everyone!

Why community consensus is tight on this one

Across 67 Access VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — niche answer (below median) (+3)

4-line Access VBA pattern (copy-ready)

Sara, have you tried casting the date/time before you update it? The data mismatch error likely comes from the fact that the hfDateRequestUpdated.Value you’re trying to insert into the database doesn’t match the column type.

Try stepping through your code and seeing what the type of that value is. If you find that it’s a string (which it seems it might be, since it’s coming from a field on a form), then you will need a check first to see if that field is the empty string (VBNullString). If so, you will want to change the value you’re inserting into the database to DBNull, which you can get in VB.Net using DBNull.Value.

We can’t see your code, so we don’t know exactly how you get the value into the database, but it would look something like this

If theDateValueBeingInserted is Nothing Then
    theDateValueBeingInserted = DBNull.Value
EndIf

Keep in mind that the above test only works if the value you get from the HiddenField is a string, which I believe it is according to the documentation. That’s probably where all this trouble you’re having is coming from. You’re implicitly converting your date/time values to a string (which is easy), but implicitly converting them back isn’t so easy, especially if the initial value was a DBNull

aside

I think what Marshall was trying to suggest was the equivalent of the above code, but in a shortcut expression called the ‘ternary operator’, which looks like this in VB.Net:

newValue = IF(oldValue is Nothing ? DBNull.Value : oldValue)

I wouldn’t recommend it though, since it’s confusing to new programmers, and the syntax changed in 2008 from IFF(condition ? trueResult : falseResult)

When to Use It — vintage (14+ years old, pre-2013)

Ranked #65th in its category — specialized fit

This pattern sits in the 90% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.

What changed between 2012 and 2026

The answer is 14 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.