Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Inserting Nulls into Date Fields MS Access 2010/2013

Inserting Nulls into Date Fields MS Access 2010/2013

Inserting Nulls into Date Fields MS Access 2010/2013

In reference to an old post in thread 705-300424, I am writing to give my solution for anyone who needs a way to insert Nulls into a Date field in a table.

Here is what I did. I used a VBA Private Sub to accomplish this goal.


Private Sub cmdAdd_Click()
    DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tbl_Test_Date (TestDate, TestText) VALUES (" _
    & IIf(IsNull(dtDate), "'" & Null & "'", "#" & dtDate & "#") & ",'" & Me.Text2 & "');"


End Sub 

In the above code I'm executing this against a form with 2 unbound text fields.
I have a subform within the main form to see the output of my code (hence the requery of the subform).
In my Test Form, I have one text field set with a format of "Short Date", and the other as a plain text with no format.
The table I am inserting the values into has 3 fields. The first field is an ID AutoNumber. The second field is my Date/Time with a format of "Short Date" (for consistency). The third and final field is a Short Text field with a maximum of 25 characters. I have had no issues inserting normal dates of 9/30/2016, or 10/31/2016. There is no reason to reference the AutoNumber field when performing an insert.

I hope this helps anyone out there who is having issues inserting nulls into a date field.

RE: Inserting Nulls into Date Fields MS Access 2010/2013

I may be wrong, but the way I see it: you have in your statement: "'" & Null & "'" so you do not insert a NULL value (unknown value) into your TestDate field in your tbl_Test_Date table, you are inserting a word Null because you have single quotes around it.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inserting Nulls into Date Fields MS Access 2010/2013


CODE --> vba

Private Sub cmdAdd_Click()
    DoCmd.SetWarnings False
    Dim strSQL as String
    strSQL = "INSERT INTO tbl_Test_Date (TestDate, TestText) VALUES (" _
    & IIf(IsNull(dtDate), "Null", "#" & dtDate & "#") & ",'" & Me.Text2 & "');"
    debug.print strSQL
    DoCmd.RunSQL strSQL


   DoCmd.SetWarnings True

End Sub 

Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close