Run Time Error 3464 Data Type mismatch in criteria expression qdf.parameter issue

mrmmickle1

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
I'm getting an error on this line of code:

Code: 
   [COLOR=#0000ff][B]Set [/B][/COLOR]qdf = db.QueryDefs("05 YTD Terms")
                qdf.Parameters("[forms]![frm001-Export]![DateEntry]") = [Forms]![frm001-Export]![DateEntry]
                qdf.Parameters("[forms]![frm001-Export]![YTDBegin]") = [Forms]![frm001-Export]![YTDBegin]
[B][COLOR=#ff0000]                Set rst = qdf.OpenRecordset[/COLOR][/B]

Not sure what's happening.

Here is the sql:

Code: 
SELECT [Termination Date]+1 AS [Term Effective Date], [tbl007-YTD Terms].[Employee Name], [tbl007-YTD Terms].[Employee Number], [tbl007-YTD Terms].[Job Function Description], [tbl007-YTD Terms].[Job Sub Function Desc], [tbl007-YTD Terms].[Employee Organization LOB], [tbl007-YTD Terms].[Employee Organization SubLOB], [tbl007-YTD Terms].[Employee Organization Department], [tbl007-YTD Terms].[Employee Organization Level E], [tbl007-YTD Terms].[Employee Organization Level F], [tbl007-YTD Terms].[Employee Organization Level G], [tbl007-YTD Terms].[Employee Organization Level H], [tbl007-YTD Terms].[Employee Organization LOB Code], [tbl007-YTD Terms].[Employee Organization SubLOB Code], [tbl007-YTD Terms].[Employee Organization Department Code], [tbl007-YTD Terms].[Employee Organization Level E Code], [tbl007-YTD Terms].[Local Pay Grade (Actual)] AS [Local Grade], [reftbl001-Grade Grouping].Group AS [Grade Equivalent], [tbl007-YTD Terms].[Employee Location Name], [tbl007-YTD Terms].[Employee Ethnic Group Description], [tbl007-YTD Terms].[Employee Gender Description], [tbl007-YTD Terms].[Sales Indicator], [tbl007-YTD Terms].[Event Group Description], [tbl007-YTD Terms].[Event Sub Group Description], [tbl007-YTD Terms].[Source Event Type Description], [tbl007-YTD Terms].[Source Event Reason Description], [tbl007-YTD Terms].[Performance Rating], [tbl007-YTD Terms].[Adjusted Service Date], [tbl007-YTD Terms].[Total Annual Base Salary], [tbl007-YTD Terms].[Employee Location Country Name], [tbl007-YTD Terms].Region, [tbl007-YTD Terms].[Sub Region]
FROM [tbl007-YTD Terms] LEFT JOIN [reftbl001-Grade Grouping] ON [tbl007-YTD Terms].[Local Pay Grade] = [reftbl001-Grade Grouping].Grade
WHERE ((([tbl007-YTD Terms].[Termination Date]) Between [COLOR=#ff0000][B][forms]![frm001-Export]![YTDBegin]-1[/B][/COLOR] And [B][COLOR=#ff0000][forms]![frm001-Export]![DateEntry]-1[/COLOR][/B]))
ORDER BY [Termination Date]+1 DESC , [tbl007-YTD Terms].[Employee Name];

When I use the immediate window the values are what I expect. I have also confirmed that they are indeed defined as a date:

1/1/2015 and 12/31/2015

Can someone please help me identify how to correct this issue?
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi,
This doesn't look like a real name for a parameter:
[forms]![frm001-Export]![DateEntry]

Is it possible the parameter is just:
[DateEntry]

I never assume anything in a form has a data type other than string unless it's bound to a table, although in this case the fact that you are substracting 1 from the value does seem to confirm it's a proper date.
 
Upvote 0
Actually, to go one step further in this sql:
Code: 
SELECT [Termination Date]+1 AS [Term Effective Date], [tbl007-YTD Terms].[Employee Name], [tbl007-YTD Terms].[Employee Number], [tbl007-YTD Terms].[Job Function Description], [tbl007-YTD Terms].[Job Sub Function Desc], [tbl007-YTD Terms].[Employee Organization LOB], [tbl007-YTD Terms].[Employee Organization SubLOB], [tbl007-YTD Terms].[Employee Organization Department], [tbl007-YTD Terms].[Employee Organization Level E], [tbl007-YTD Terms].[Employee Organization Level F], [tbl007-YTD Terms].[Employee Organization Level G], [tbl007-YTD Terms].[Employee Organization Level H], [tbl007-YTD Terms].[Employee Organization LOB Code], [tbl007-YTD Terms].[Employee Organization SubLOB Code], [tbl007-YTD Terms].[Employee Organization Department Code], [tbl007-YTD Terms].[Employee Organization Level E Code], [tbl007-YTD Terms].[Local Pay Grade (Actual)] AS [Local Grade], [reftbl001-Grade Grouping].Group AS [Grade Equivalent], [tbl007-YTD Terms].[Employee Location Name], [tbl007-YTD Terms].[Employee Ethnic Group Description], [tbl007-YTD Terms].[Employee Gender Description], [tbl007-YTD Terms].[Sales Indicator], [tbl007-YTD Terms].[Event Group Description], [tbl007-YTD Terms].[Event Sub Group Description], [tbl007-YTD Terms].[Source Event Type Description], [tbl007-YTD Terms].[Source Event Reason Description], [tbl007-YTD Terms].[Performance Rating], [tbl007-YTD Terms].[Adjusted Service Date], [tbl007-YTD Terms].[Total Annual Base Salary], [tbl007-YTD Terms].[Employee Location Country Name], [tbl007-YTD Terms].Region, [tbl007-YTD Terms].[Sub Region]
FROM [tbl007-YTD Terms] LEFT JOIN [reftbl001-Grade Grouping] ON [tbl007-YTD Terms].[Local Pay Grade] = [reftbl001-Grade Grouping].Grade
WHERE ((([tbl007-YTD Terms].[Termination Date]) Between [forms]![frm001-Export]![YTDBegin]-1 And [forms]![frm001-Export]![DateEntry]-1))
ORDER BY [Termination Date]+1 DESC , [tbl007-YTD Terms].[Employee Name];

You don't need to define or set parameters, they will run as is as long as the form is open, because the query will get the values from the form "automatically".
 
Upvote 0
xenou,


It's been a while since we have spoken. Hope all is well. I appreciate you taking a look at my inquiry. I tried a few things you suggested may have been in err and I got the following results:

Before running the code I ensured that the form is indeed open.

These are the declarations:

Code: 
    [COLOR=#0000ff][B]Dim [/B][/COLOR]qdf               [B][COLOR=#0000ff]As [/COLOR][/B]QueryDef
   [COLOR=#0000ff][B] Dim [/B][/COLOR]db               [COLOR=#0000ff][B] As [/B][/COLOR]Database
    [COLOR=#0000ff][B]Dim[/B][/COLOR] rst               [COLOR=#0000ff][B]As [/B][/COLOR]Recordset
When I use just these two lines:

Code: 
[COLOR=#0000ff][B]Set[/B][/COLOR] qdf = db.QueryDefs("05 YTD Terms")
[COLOR=#0000ff][B]Set[/B][/COLOR] rst = qdf.OpenRecordset

I get a Run Time 3061 error Too few parameters. Expected 2.

When I change the code to this:

Code: 
[B][COLOR=#0000ff]Set[/COLOR][/B] rst = db.OpenRecordset("05 YTD Terms")

I get the same exact error and description

When I change the qdf.Parameters to this:

Code: 
 qdf.Parameters("[DateEntry]") = [Forms]![frm001-Export]![DateEntry]
 qdf.Parameters("[YTDBegin]") = [Forms]![frm001-Export]![YTDBegin]
I get:

Run Time Error 3265 item not found in this collection....

When I try this:

Code: 
     strSQL = "SELECT [Termination Date]+1 AS [Term Effective Date], [tbl007-YTD Terms].[Employee Name],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Number], [tbl007-YTD Terms].[Job Function Description],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Job Sub Function Desc], [tbl007-YTD Terms].[Employee Organization LOB],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization SubLOB], [tbl007-YTD Terms].[Employee Organization Department],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization Level E], [tbl007-YTD Terms].[Employee Organization Level F],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Organization Level G], [tbl007-YTD Terms].[Employee Organization Level H],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Organization LOB Code], [tbl007-YTD Terms].[Employee Organization SubLOB Code],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization Department Code], [tbl007-YTD Terms].[Employee Organization Level E Code],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Local Pay Grade (Actual)] AS [Local Grade], [reftbl001-Grade Grouping].Group AS [Grade Equivalent],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Location Name], [tbl007-YTD Terms].[Employee Ethnic Group Description],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Gender Description], [tbl007-YTD Terms].[Sales Indicator], [tbl007-YTD Terms].[Event Group Description],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Event Sub Group Description], [tbl007-YTD Terms].[Source Event Type Description],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Source Event Reason Description], [tbl007-YTD Terms].[Performance Rating],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Adjusted Service Date], [tbl007-YTD Terms].[Total Annual Base Salary],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Location Country Name] , [tbl007-YTD Terms].Region, [tbl007-YTD Terms].[Sub Region]"
                strSQL = strSQL & vbLf & "FROM [tbl007-YTD Terms] LEFT JOIN [reftbl001-Grade Grouping] ON [tbl007-YTD Terms].[Local Pay Grade] = [reftbl001-Grade Grouping].Grade"
                strSQL = strSQL & vbLf & "WHERE ((([tbl007-YTD Terms].[Termination Date]) Between " & [Forms]![frm001-Export]![YTDBegin]
                strSQL = strSQL & vbLf & " -1 And " & [Forms]![frm001-Export]![DateEntry] & " -1))"


                [COLOR=#0000ff][B]Set[/B][/COLOR] rst = db.OpenRecordset(strSQL, 3)

i get a run time error 3001 'invalid argument'

When I run the query manually with the form up I get a bunch of records....just as I expect too....

Not sure what's going on is there another way to export a query to a specific worksheet? I have a template that has macros in it so I am trying to export X query to X worksheet....

http://www.mrexcel.com/forum/microsoft-access/930541-export-access-query-specific-worksheet.html


There are about 15 to queries I need to export. 13 out of 15 work without issue.
 
Last edited:
Upvote 0
Okay.
are you defining rst as a DAO recordset?
 
Upvote 0
Okay, so this works, I think:
Code: 
Sub foo()
Dim rst As DAO.Recordset
Dim qdf As QueryDef
    
    Set qdf = CurrentDb.QueryDefs("Query2")
    Debug.Print qdf.SQL
    
    qdf.Parameters(0).Value = [Forms]![Form1]![Text0]
    Set rst = qdf.OpenRecordset
    If Not rst.EOF Then
        Debug.Print rst.Fields(0).Value & " | " & rst.Fields(1).Value
    End If
    
    rst.Close
    Set rst = Nothing

End Sub

I didn't realize that you would not get the same effect with opening a recordset as you would just running the query but so it seems.
Since you have a date, I might put the param value in a CDate() function to be sure of the data type being properly assigned and stored.

It also works with your original syntax:
Code: 
qdf.Parameters("[Forms].[Form1].[Text0]").Value = [Forms]![Form1]![Text0]

The only problem is that this is what you had originally, so that's confusing. It worked for me with a simple test. Can you strip down the query to the basics and test again?



Note that if you take this approach:
Code: 
                strSQL = strSQL & vbLf & "WHERE ((([tbl007-YTD Terms].[Termination Date]) Between " & [Forms]![frm001-Export]![YTDBegin]
                strSQL = strSQL & vbLf & " -1 And " & [Forms]![frm001-Export]![DateEntry] & " -1))"
Then you are basically using date literals to build a sql string and date literals must be enclosed in hashes (#).
 
Last edited:
Upvote 0
xenou,

When I use this it will work... :

Code: 
 strSQL = "SELECT [Termination Date]+1 AS [Term Effective Date], [tbl007-YTD Terms].[Employee Name],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Number], [tbl007-YTD Terms].[Job Function Description],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Job Sub Function Desc], [tbl007-YTD Terms].[Employee Organization LOB],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization SubLOB], [tbl007-YTD Terms].[Employee Organization Department],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization Level E], [tbl007-YTD Terms].[Employee Organization Level F],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Organization Level G], [tbl007-YTD Terms].[Employee Organization Level H],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Organization LOB Code], [tbl007-YTD Terms].[Employee Organization SubLOB Code],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Organization Department Code], [tbl007-YTD Terms].[Employee Organization Level E Code],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Local Pay Grade (Actual)] AS [Local Grade], [reftbl001-Grade Grouping].Group AS [Grade Equivalent],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Location Name], [tbl007-YTD Terms].[Employee Ethnic Group Description],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Employee Gender Description], [tbl007-YTD Terms].[Sales Indicator], [tbl007-YTD Terms].[Event Group Description],"
                strSQL = strSQL & vbLf & "[tbl007-YTD Terms].[Event Sub Group Description], [tbl007-YTD Terms].[Source Event Type Description],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Source Event Reason Description], [tbl007-YTD Terms].[Performance Rating],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Adjusted Service Date], [tbl007-YTD Terms].[Total Annual Base Salary],"
                strSQL = strSQL & vbLf & " [tbl007-YTD Terms].[Employee Location Country Name] , [tbl007-YTD Terms].Region, [tbl007-YTD Terms].[Sub Region]"
                strSQL = strSQL & vbLf & "FROM [tbl007-YTD Terms] LEFT JOIN [reftbl001-Grade Grouping] ON [tbl007-YTD Terms].[Local Pay Grade] = [reftbl001-Grade Grouping].Grade"
                strSQL = strSQL & vbLf & "WHERE ((([tbl007-YTD Terms].[Termination Date]) Between"
                strSQL = strSQL & vbLf & "#" & [Forms]![frm001-Export]![YTDBegin] & "# -1"
                strSQL = strSQL & vbLf & "And #" & [Forms]![frm001-Export]![DateEntry] & "# -1))"

    [COLOR=#0000ff]Set [/COLOR]rst = db.OpenRecordset(strSQL)

However, when I use this I still get the odd error 3464 data type mismatch:

Code: 
                qdf.Parameters("[forms]![frm001-Export]![DateEntry]") = [COLOR=#0000ff]CDate[/COLOR]([Forms]![frm001-Export]![DateEntry])
                qdf.Parameters("[forms]![frm001-Export]![YTDBegin]") = [COLOR=#0000ff]CDate[/COLOR]([Forms]![frm001-Export]![YTDBegin])

Seems awful strange to me.

When I printed the qdf.sql I didn't see anything either (looked at this several times before posting the initial question)...

In addition here is one other odd thing:

When I run the query manually while in break mode the query results in 0 records.

When I run the query manually with the form loaded it results in many records....

Both text boxes are also set to short date
 
Last edited:
Upvote 0
Running some more tests, this time with date parameters (no longer being lazy).

I can confirm this works for me:
Code: 
    Set qdf = CurrentDb.QueryDefs("Query2")
    With qdf
        .Parameters(0) = CDate([Forms]![Form1]![Text0]) - 1
        .Parameters(1) = CDate([Forms]![Form1]![Text4]) - 1
    End With

But this does not:
Code: 
    Set qdf = CurrentDb.QueryDefs("Query2")
    With qdf
        .Parameters(0) = [Forms]![Form1]![Text0] - 1
        .Parameters(1) = [Forms]![Form1]![Text4] - 1
    End With


So this confirms my suspicion that you should always treat textboxes as strings unless they are bound to data tables with defined types, and the original problem may have been not explicitly casting the textbox values to dates.

My whole test code is (you can refer to the parameters by name, too, rather than index):
Code: 
Sub foo()
Dim rst As DAO.Recordset
Dim qdf As QueryDef
    
    Set qdf = CurrentDb.QueryDefs("Query2")
    With qdf
        .Parameters(0) = CDate([Forms]![Form1]![Text0]) - 1
        .Parameters(1) = CDate([Forms]![Form1]![Text4]) - 1
    End With
    
    With qdf.Parameters(0)
        Debug.Print .Name
        Debug.Print .Value
    End With
    With qdf.Parameters(1)
        Debug.Print .Name
        Debug.Print .Value
    End With
    
    Set rst = qdf.OpenRecordset
    If Not rst.EOF Then
        Debug.Print rst.Fields(0).Value & " | " & rst.Fields(1).Value & " | " _
                & rst.Fields(2).Value & " | " & rst.Fields(3).Value
    End If
    
    rst.Close
    Set rst = Nothing
    
End Sub
 
Upvote 0
Hi,
just posted my last response after yours but written before your last post.

As far as Short Date, that's a formatting type, not a data type, if I am not mistaken so I still suspect your textboxes as being String data.

The working version with the SQL created with string literals is working now so that's good! And you can also use CDate there too (I see no difference in choosing one way or the other - potentially not invoking a function in the query would be more efficient):
Code: 
                strSQL = strSQL & vbLf & "CDate(" & [Forms]![frm001-Export]![YTDBegin] & ")-1"
 
Last edited:
Upvote 0
xenou,

Thanks so much for the help. While hardcoding sql isn't ideal at least the variables from the form still work properly. :) Which will prevent me from having to hard code the form values every time !!!
On a tight timeline on this project. Really came through in the clutch! Thanks again


P.S. Working at another company now. Don't have the same crazy invisible character issue we spoke about previously ahahah What a nightmare that was :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

mrmmickle1
  • Question
Employee Turnover / Dax Formulas / PowerPivot Model Suggestions
Replies
3
Views
9K
mrmmickle1
mrmmickle1
M
  • Question
Ho can I improve the time taken to paste the data?
Replies
1
Views
440
Andrew Poulsom
Andrew Poulsom

Forum statistics

Threads
1,215,455
Messages
6,124,935
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top