Access Query SQL Type mismatch error when trying to update query via VBA

Question

I have a query that takes multiple criteria from comboboxes on a form.

In order to alter the sort of the query for reporting purposes I use the below code:

Dim oDB As Database 
Dim oQuery As QueryDef
Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs("qry_AdjustmentDataSplitGroup")
oQuery.SQL = ("SELECT tbl_AdjustmentData.POLICY, tbl_AdjustmentData.MD, tbl_AdjustmentData.[EFF DTE], tbl_AdjustmentData.NAME, tbl_AdjustmentData.[DEP/PREM], tbl_AdjustmentData.[LST PREM], tbl_AdjustmentData.GROUP, tbl_AdjustmentData.[WA DATE] FROM tbl_AdjustmentData WHERE (((tbl_AdjustmentData.Group) Is Not Null)) ORDER BY tbl_AdjustmentData.[WA DATE];")
Set oQuery = Nothing
Set oDB = Nothing

And change the ORDER BY depending on what button is pressed on the form. This works OK.

However, I thought it would be the same if I needed to alter the query criteria (or the WHERE) I just change that line like I can change the ORDER BY part of the code.

When I use the below code for a different query:

Dim oDB As Database
Dim oQuery As QueryDef
Set oDB = CurrentDb
Set oQuery = oDB.QueryDefs("qry_AdjustmentDataSplitNonGroupApr")
oQuery.SQL = ("SELECT qry_AdjustmentDataSplitNonGroup.POLICY, qry_AdjustmentDataSplitNonGroup.MD, qry_AdjustmentDataSplitNonGroup.[EFF DTE], qry_AdjustmentDataSplitNonGroup.NAME, qry_AdjustmentDataSplitNonGroup.[DEP/PREM], qry_AdjustmentDataSplitNonGroup.[LST PREM], qry_AdjustmentDataSplitNonGroup.[WA DATE], Right([EFF DTE],2) & " / " & Mid([EFF DTE],5,2) & " / " & Left([EFF DTE],4) AS EffectiveDate, Right([WA DATE],2) & " / " & Mid([WA DATE],5,2) & " / " & Left([WA DATE],4) AS WADate, Left([EFF DTE],4) AS [Year], Left([WA DATE],4) AS WAYear FROM qry_AdjustmentDataSplitNonGroup WHERE (((Left([EFF DTE],4))=[Forms]![frm_Index]![cbo_YearPicker]) AND ((Left([WA DATE],4))=[Forms]![frm_Index]![cbo_WAPicker]) AND ((Mid([EFF DTE],5,2))=""04""));")
Set oQuery = Nothing
Set oDB = Nothing

I want to be able to remove this part:

AND ((Left([WA DATE],4))=[Forms]![frm_Index]![cbo_WAPicker])

Using an option button on the form, leaving the rest the same.

The problem though, is when I try this I get a 'Type mismatch' error on the line beginning oQuery.SQL.

I assume it's to do with the section where Mid([EFF DTE],5,2))=""04"" and I've tried double quotes as in this case, I've tried single quotes and I've tried no quotes at all changing the record in the Table from Text to Number, but always get the same error.

Is there an easier way to do this? Or can my code be ammended to work correctly?

Final note, the reason for the option button at all is to remove the 'WA Date' criteria from the query as that criteria reads from a combobox and when I fiddled with the combobox to allow an ALL option it actually returned no results.

I've heard that UNION ALL might be another way round this problem so as to avoid having to mess about removing the criteria altogether but I'm not sure how to implement that.

HansUp · Accepted Answer · 2015-02-05 04:20:18Z

The situation you're facing is easy to misinterpret.

In your second code sample, you get a "type mismatch" error at the line similar to this abbreviated version:

oQuery.SQL = ("SELECT qry_AdjustmentDataSplitN ... ")

You might think that is Access complaining that your SELECT statement is not valid. But the actual problem is similar to this example from the Immediate window, which also throws a "type mismatch" error:

? "a" / "b"

That happens because you can't divide one string by another string. Division only makes sense with numerical values.

When you examine your code with that issue in mind, you should find multiple cases of this pattern:

"some text & " / " & more text"

Compare that to my "a" / "b" example.

You need to quote it differently. I will take a stab at what I think you want. I'm unsure whether the statement logic is correct, but at least this produces a valid VBA string:

Dim strSelect As String
strSelect = "SELECT q.POLICY, q.MD, q.[EFF DTE], q.NAME, " & _
    "q.[DEP/PREM], q.[LST PREM], q.[WA DATE], " & _
    "(Right([EFF DTE],2) & '/' & Mid([EFF DTE],5,2) & '/' & Left([EFF DTE],4)) AS EffectiveDate, " & _
    "(Right([WA DATE],2) & '/' & Mid([WA DATE],5,2) & '/' & Left([WA DATE],4)) AS WADate, " & _
    "Left([EFF DTE],4) AS [Year], Left([WA DATE],4) AS WAYear" & vbCrLf & _
    "FROM qry_AdjustmentDataSplitNonGroup AS q" & vbCrLf & _
    "WHERE Left([EFF DTE],4)=[Forms]![frm_Index]![cbo_YearPicker]" & vbCrlf & _
    "AND Mid([EFF DTE],5,2)='04';"
Debug.Print strSelect ' <-- view the completed statement text in Immediate window
                      ' Ctrl+g will take you there
oQuery.SQL = strSelect

And finally, reading between the lines, I'm guessing your EFF DTE and WA DATE values are dates as text in yyyy/mm/dd format and you want the query to display them in dd/mm/yyyy format. If that is true, compare these two expressions which should both return the same string:

Right([EFF DTE], 2) & '/' & Mid([EFF DTE], 5, 2) & '/' & Left([EFF DTE], 4)
Format([EFF DTE], 'dd/mm/yyyy')

Thank you for such a concise and effective solution HansUp. Not only has your code worked perfectly but your explanation is superb. It also handy to know a faster method of formatting the dates correctly. Much appreciated.

Collectives™ on Stack Overflow

Access Query SQL Type mismatch error when trying to update query via VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related