“Run-time error 3464” - Data type mismatch in criteria expression

Question

[Below I am setting the parameters in vba to send to an access query. The query takes a Start Date, End Date, Reason Code and Sub Group. All the criterion are String except for Sub Group which is an integer.

Sub FinalDrill()

'Step 1: Declare your variables
Dim DatabasePath As String
DatabasePath = ThisWorkbook.Path & "\SpreadSheetData.accdb"
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(DatabasePath)
Set MyQueryDef = MyDatabase.QueryDefs("FinalDrill")

'Step 3: Define the Parameters


With MyQueryDef
    .Parameters("[Start Date]") = GetStartDate()
    .Parameters("[End Date]") = GetEndDate()
    .Parameters("[Reason Code]") = "C"
    .Parameters("[Sub Group]") = 2
End With



'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

The final line throws "Run-time error 3464" - Data type mismatch in criteria expression

I don't get it because i have run this exact type of query from different subs with just the Start Date and End Date and it works a treat.... even is i comment out the

'.Parameters("[Reason Code]") = "C"
'.Parameters("[Sub Group]") = 2

and change the access query to only except the Start Date and End Date I get the same error.

Maybe I'm tired and missing something obvious, any help would be great.

UPDATE

The below query works so I'm guessing the problem is in my access query.

'Step 1: Declare your variables
Dim DatabasePath As String
DatabasePath = ThisWorkbook.Path & "\SpreadSheetData.accdb"
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(DatabasePath)
Set MyQueryDef = MyDatabase.QueryDefs("levelOnePie")

'Step 3: Define the Parameters


With MyQueryDef
    .Parameters("[Start Date]") = GetStartDate()
    .Parameters("[End Date]") = GetEndDate()
End With



'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

Will update with more results once i find them.

If you comment date parameters is error still there? I guess that you have problems with dates. Does your function GetStartDate() return date or string value? — www
Typo? .Parameters("Sub Group]") instead of .Parameters("[Sub Group]") — barrowc
@Wawrzyniec - GetStartDate() and GetEndDate() work in the below query the only thing different is the query name in access — Zac
@barrowc - yes typo but like i said even commenting out the two last Parameters and change the access query to only except start and end dates it still throws the error — Zac

Zac Zac · Accepted Answer · 2013-03-11T01:03:49

OK, even though the Functions GetStartDate() and GetEndDate() work for previous query's in other subs(), I thought about what Wawrzyniec said and changed the date Parameters to the cell that holds them.

With MyQueryDef
    .Parameters("[Start Date]") = Sheets("DASHBOARD").[C4]
    .Parameters("[End Date]") = Sheets("DASHBOARD").[C5]
    .Parameters("[Reason Code]") = "C"
    .Parameters("[Sub Group]") = 2
End With

The reason I had these Functions to get the date is that a feature in the dashboard was if the StartDate cell was empty it would use the date the data begins, and no end date meant it would use today's date.

As two other drill downs are executed before this one i will just place the dates in the cells so they can be grabbed in this final drill down.

“Run-time error 3464” - Data type mismatch in criteria expression

1 Answers