Data Type Conversion Error 3421: Parameter Query

[Sonnydl]

Well, I'm flummoxed here. I'm trying to execute an append query in which I use an input box to get a date I'm adding to the data.

When I run the query outside of VBA, I am prompted for the date and it works as expected. Not so much when trying to execute it through VBA. I'm trying to import credit card transactions from an Excel file, then add the Statement Closing Date.

Here's my code:

Private Sub cmdImportsAmexTx_Click()

Dim xls As Excel.Application
Dim wbk As Excel.Workbook
Dim xlRange As Excel.Range
Dim sFile As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dtClose As String
Dim lRows As Long

Set db = CurrentDb

On Error GoTo Err_ImportAMEXTx

db.Execute "qryAmexTxTempDELETE"

[COLOR="Green"]'[Code that gets the Excel file][/COLOR]

Do
    dtClose = InputBox("Enter the Statement Closing Date as mm/dd/yyyy", "Input Required")
    If Len(dtClose) = 0 Then
        MsgBox "The import process has been cancelled.", vbExclamation + vbOKOnly
        GoTo Exit_ImportAMEXTx
    End If
    If Not IsDate(dtClose) Then
        If vbCancel = MsgBox("You need to enter a proper date.", vbExclamation + vbOKCancel, "Invalid Input") Then GoTo Exit_ImportAMEXTx
    End If
Loop While Not (IsDate(dtClose))

[COLOR="green"]'[Code to prep the Excel file][/COLOR]

[COLOR="green"]'---Import the file into a TEMP folder - Becuase the fields don't match up with the final table---[/COLOR]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblAmexTransactionsTEMP", sFile, True

Set qdf = db.QueryDefs("qryAmexTransactionsAppend")

qdf.Parameters(0) = Format(dtClose, "Short Date")

[COLOR="green"]'I've also tried qdf.Parameters(0) = cDate(dtClose), and qdf.Parameters(0) = "#" & dtClose & "#" and declaring DIM dtClose as Date initially.

'Before I added in the ClosingDate field, the update query worked, but then I was just doing a db.execute as I had no parameters. I also added a ClosingDate field to the TEMP table and formatted it as a date, which did not resolve the error.[/COLOR]

[COLOR="green"]'---This next line is where it fails---[/COLOR]
[COLOR="Red"]qdf.Execute "qryAmexTransactionsAppend"[/COLOR]
lRows = qdf.RecordsAffected
MsgBox "You added " & lRows & " records."

Exit_ImportAMEXTx:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

Err_ImportAMEXTx:
    If Err.Number = 5 Then 'For cancelling the selection of the Excel file
        GoTo Exit_ImportAMEXTx
    Else
        MsgBox Err.Number & " - " & Err.Description, vbCritical
        Resume Exit_ImportAMEXTx
    End If
End Sub

I just don't know what's amiss here.

This is the SQL-version of the Append Query, in case it's of use:
INSERT INTO tblAmexTransactions ( [Date], Receipt, Description, Amount, ReceiptURL, ExtendedDetails, DoingBusinessAs, ParentCompany, Address, PostCodeCountry, Reference, Category, ClosingDate )
SELECT tblAmexTransactionsTEMP.Date, tblAmexTransactionsTEMP.Receipt, tblAmexTransactionsTEMP.Description, tblAmexTransactionsTEMP.Amount, tblAmexTransactionsTEMP.[Receipt URL], tblAmexTransactionsTEMP.[Extended Details], tblAmexTransactionsTEMP.[Doing Business As], tblAmexTransactionsTEMP.[Parent Company], tblAmexTransactionsTEMP.[Street Address], tblAmexTransactionsTEMP.[City, State Zip], tblAmexTransactionsTEMP.Reference, tblAmexTransactionsTEMP.Category, [Input] AS ClosingDate
FROM tblAmexTransactionsTEMP
WHERE ((([Input])=[Input]));

Thanks in advance!

 

[Galaxiom]

Your parameter needs to be passed a date as #mm/dd/yyyy#

Format will pass a string in the Regional Short Date format.

 

[Sonnydl]

Your parameter needs to be passed a date as #mm/dd/yyyy#

Thanks for the reply. I did attempt that, or so I thought:

qdf.Parameters(0) = "#" & dtClose & "#"

Did I miss something?

Actually I might have done it as:

qdf.Parameters(0) = "#" & CDate(dtClose) & "#"

So maybe that's where I went wrong? I thought I tried various combinations of the above, as well.

 

[Sonnydl]

I tried:

qdf.Parameters(0) = "#" & dtClose & "#"

and

qdf.Parameters(0) = "#" & Format(dtClose, "mm/dd/yyyy") & "#"

and plain

qdf.Parameters(0) = CDate(dtClose)

No joy.

 

[JHB]

Could you post your database with some sample data, zip it because you haven't post 10 post yet?

 

[Sonnydl]

Could you post your database with some sample data, zip it because you haven't post 10 post yet?

I don't think that I can, as it's a pretty big database with with tons of private information in it, and this is only one small part. I think it would take me longer to figure out how to do that than it's worth my time or your time in going through it afterwards.

Maybe I can create a simple mock dbase with the Temp table and the target table tomorrow.

If I do a simple DoCmd.OpenQuery, then I get the warning, get prompted for the date, but it works. I was just looking for a solution that would allow me to:

1. Execute the query (e.g. no warnings)
2. Have the user input the closing date BEFORE they altered the Excel file, as they cannot re-run the code once the file has been altered.

 

[arnelgp]

You have two [input] on the where clause. Try qualifying both with tablename, eg:

Where table2.input=table2.input

 

[Sonnydl]

You have two [input] on the where clause. Try qualifying both with tablename

I altered that, too, but I still get the error.

 

[NauticalGent]

I has a very similar issue before. While I was trouble shooting it, I assigned the date input to a date variable and then used the variable as the parameter. My intent was to use Debug.Print so that I could see what was being passed.

I was pleasantly surprised that it fixed this issue. Who knows what mischief the Access Gnomes get up to...?

 

[JHB]

Remove the name of the query when you execute it, because you've assign it with the Set.
qdf.Execute

 

[Sonnydl]

Remove the name of the query when you execute it, because you've assign it with the Set.
qdf.Execute

That seems to have done the trick! Thank you so much. I was using code that I'd seen from another Access site on how to Execute a parameter query, and thought that the syntax was there to include the query name. Clearly I misunderstood something.

That saves me putting together a mock database for you. In the end, this is what the code looks like:

'All the code above stays the same

Set qdf = db.QueryDefs("qryAmexTransactionsAppend")
qdf.Parameters(0) = CDate(dtClose)
qdf.Execute

'The rest of the code

My append query ended up like this:

INSERT INTO tblAmexTransactions ( [Date], Receipt, Description, Amount, ReceiptURL, ExtendedDetails, DoingBusinessAs, ParentCompany, Address, PostCodeCountry, Reference, Category, ClosingDate )
SELECT tblAmexTransactionsTEMP.Date, tblAmexTransactionsTEMP.Receipt, tblAmexTransactionsTEMP.Description, tblAmexTransactionsTEMP.Amount, tblAmexTransactionsTEMP.[Receipt URL], tblAmexTransactionsTEMP.[Extended Details], tblAmexTransactionsTEMP.[Doing Business As], tblAmexTransactionsTEMP.[Parent Company], tblAmexTransactionsTEMP.[Street Address], tblAmexTransactionsTEMP.[City, State Zip], tblAmexTransactionsTEMP.Reference, tblAmexTransactionsTEMP.Category, [Enter Statement Closing date - m/d/yy] AS [Input]
FROM tblAmexTransactionsTEMP;

Note: I had removed the double [INPUT] earlier in the troubleshooting process and this had not resolved the issue 3421 error. Doing it as above gave the end user insight into what to do when running the query via DoCmd.OpenQuery.

 

[NauticalGent]

Remove the name of the query when you execute it, because you've assign it with the Set.
qdf.Execute

Nice catch! I would have thought it would have thought it would have thrown a different error code, but there it is.

 

[JHB]

.. I would have thought it would have thought it would have thrown a different error code, but there it is.

Yeah, but still the error code make sense, because it can have an option behind the execute part like "qdf.Execute dbFailOnError" and dbFailOnError/options is/are a number(s). So when you've apply the query name as option (which is text), you'll get a "Data Type Conversion Error".

 

[NauticalGent]

Well, when you put it that way...

Very impressive, thanks for the explanation.