Fix Access Error 3421 in a VBA Parameter Query
Question details
Run an Access append query from VBA without a data type conversion error when supplying a closing date.

- 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.
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.
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.
In the Access Navigation Pane, right-click the saved append query and select SQL View. Add `PARAMETERS [Input] DateTime;` before the INSERT INTO statement.
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.
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.
Save the query and run the VBA procedure. If error 3421 remains, check the source expressions and destination field types for other conversion mismatches.

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.

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.




