VBA & Macro Problems

How to Fix VBA INSERT INTO Errors with Blank Access Date Fields

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user is experiencing failures with a VBA INSERT INTO process when attempting to copy records containing blank source date fields, and needs to preserve these blank values.

How to Fix VBA INSERT INTO Errors with Blank Date Fields in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Copying database records using VBA where some date fields do not contain a value, requiring the process to handle empty data gracefully.
Observed behavior: The INSERT INTO process fails and throws an error because it cannot properly handle or map the blank values (Null) being inserted into the destination date fields.

Before you start

Before troubleshooting the VBA code, verify that the destination table's design settings allow Null values (Required property set to 'No') for the target date fields.

Solution 1Recommended

Use an Append Query (INSERT INTO) Directly

Executing a direct SQL Append query is usually more efficient and handles Null values more reliably than looping through recordsets in VBA.

Instead of reading each source row into multiple variables and writing them to another recordset, you can replace the loop entirely with a direct INSERT INTO SQL query. SQL natively handles moving Null values from a source table to a destination table without type mismatch errors.

Open your VBA Module

Navigate to the Database Tools tab, click on Visual Basic to open the VBA editor, and locate the module containing your copy process.

Construct the SQL Statement

Write an INSERT INTO statement that maps the source table fields to the destination table. For example: strSQL = "INSERT INTO DestTable (DateField1, Field2) SELECT DateField1, Field2 FROM SourceTable WHERE..."

Execute the Query

Run the SQL command using the CurrentDb.Execute method: CurrentDb.Execute strSQL, dbFailOnError. This ensures that errors are trapped and Nulls are preserved automatically.

Use an Append Query (INSERT INTO) Directly

Null vs. Zero-Length String: In Access, a 'blank' date is a Null value, not an empty string (""). Date fields can only contain a valid date or Null.

Solution 2

Handle Null Values Properly Using Recordsets

If your process requires iterating through recordsets, ensure variables are correctly typed to accept Nulls and use the AddNew method without calling Edit.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite with Excellent Compatibility?

While Microsoft Access is designed for complex relational databases, a vast majority of data management and VBA tasks can be efficiently executed using spreadsheets. WPS Office is a powerful, free alternative to Microsoft Office that includes robust support for VBA and macros in its spreadsheet application.

1. Download WPS Office: Visit the official WPS website and click the free download button for your operating system.
2. Install the Suite: Run the installer and follow the quick setup wizard to install WPS Writer, Spreadsheets, and Presentation.
3. Open WPS Spreadsheets: Launch WPS Spreadsheets to start managing your data, executing macros, and utilizing VBA scripts efficiently.

Free and lightweight office suite with a fast installation processExcellent compatibility with Microsoft Office formats (XLSX, DOCX, PPTX)Familiar user interface for seamless and zero-learning-curve migrationAdvanced Spreadsheet capabilities with robust VBA and Macro support

Frequently Asked Questions

Why does VBA throw a 'Type Mismatch' error with blank date fields?

In Access, a blank date field represents a Null value. If you attempt to assign this Null value to a VBA variable strictly declared as a 'Date' or 'String', VBA cannot process it and throws a Type Mismatch error. You must use 'Variant' data types to store Nulls.

How can I check if a date field is blank before inserting it using VBA?

You can use the IsNull() function in your VBA code. For example, using 'If IsNull(rs!DateField) Then' allows you to conditionally process the data or assign a default value before running the INSERT statement.

Can I insert a zero-length string ("") into an Access date field?

No. Access date fields are strictly typed to only accept valid dates or Null values. Attempting to insert a zero-length string will result in a data validation or type mismatch error.

Excel & Data Problems