Others

Fix Access Error 3421 in a VBA Parameter Query

Huda QurayshiHuda Qurayshi Nov 20, 2026 869 views

Question details

Run an Access append query from VBA without a data type conversion error when supplying a closing date.

Fix Access Error 3421 in a VBA Parameter Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A saved append query uses an input parameter for the closing date and is executed from VBA.
Observed behavior
The query works when run manually but returns data type conversion error 3421 when run from VBA.
Before you start

Open the saved append query in SQL View and identify its closing-date parameter. Check that the destination ClosingDate field has the Date/Time data type and that the VBA value represents a valid date.

Solution 1Recommended

Declare and pass the closing-date parameter as a Date

Use this approach when Access cannot convert the VBA value supplied to the append query's closing-date parameter.

1
Declare the parameter

In the Access Navigation Pane, right-click the saved append query and select SQL View. Add `PARAMETERS [Input] DateTime;` before the INSERT INTO statement.

2
Check the query expression

In SQL View, use `[Input] AS ClosingDate` in the SELECT list for the destination ClosingDate field. Remove any WHERE condition that only compares `[Input]` with itself, while retaining filters needed for the append.

3
Pass a Date value in VBA

In the VBA editor, open the code that runs the query and assign the parameter before execution, for example: `Set qdf = CurrentDb.QueryDefs("YourQueryName")`, then `qdf.Parameters("Input").Value = CDate(dtClose)`, followed by `qdf.Execute dbFailOnError`. Replace YourQueryName with the saved query's name.

4
Run the query again

Save the query and run the VBA procedure. If error 3421 remains, check the source expressions and destination field types for other conversion mismatches.

Declare and pass the closing-date parameter as a Date
Use a valid date value: Prefer a VBA Date variable over a formatted string. If dtClose is text, CDate must be able to interpret it according to the system's regional date settings.
Free Microsoft Office alternative

Work with Office documents in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with documents, spreadsheets, and presentations. Its Office format support helps you move existing files with minimal disruption. It does not fix Microsoft Access VBA queries.

Use a familiar interface for everyday office tasksOpen and edit Microsoft Word, Excel, and PowerPoint formatsWork with spreadsheet data alongside database-related reportsUse a lightweight office suite for common document workflows
microsoft office alternative - wps office

Frequently Asked Questions

Why does the query work manually but fail from VBA?

The value entered manually and the value passed by VBA may be interpreted as different data types. Declare `[Input]` as DateTime in the query's SQL View and pass a valid Date value through the QueryDef parameter.

How do I declare the date parameter in Access?

Right-click the saved query in the Navigation Pane, choose SQL View, and place `PARAMETERS [Input] DateTime;` before the INSERT INTO statement. Use the same parameter name in the query and VBA.

Can I pass a formatted date string to the query?

A VBA Date value is preferable. If you use `CDate` on a string, make sure the string is valid and unambiguous under the system's regional date settings.

What should I check if error 3421 continues?

In the destination table's Design View, check the ClosingDate field type. Then review the other SELECT expressions and their corresponding destination fields for incompatible values or data types.