Error in InStrRev with respect to VarType

The "Type Mismatch" error you are encountering in your Access SQL query when using the InStrRev function could be due to how Access evaluates expressions in the HAVING clause. The HAVING clause is used to filter records after the GROUP BY operation, and sometimes the evaluation of expressions can lead to unexpected results, especially if there are null values or data type inconsistencies in the fields being compared.

In your first example, the expression 1 * InStrRev ([Query21].[Naam tegenrekening], "Broek") is evaluated, and if it results in a null or unexpected value, it may trigger the type mismatch error. However, when you add an additional condition (e.g., AND ((mutations.grootboek) = 4120)), it may change the context of evaluation, allowing Access to handle the expression without error.

To troubleshoot this issue:

1. Ensure that [Query21].[Naam tegenrekening] does not contain null values, as this can lead to type mismatches when performing string operations.
2. Consider using Nz() function to handle null values, for example: Nz(1 * InStrRev([Query21].[Naam tegenrekening], "Broek"), 0) to provide a default value in case of null.
3. Check the data types of the fields involved in the comparison to ensure they are compatible.

By addressing these points, you should be able to resolve the type mismatch error in your query.

References:

• Type mismatch (Visual Basic)
• VarType function

As you have found that adding an additional criterion solves the problem, in situations where there is no additional criterion to be added you could try:

InStrRev ([Query21].[Naam tegenrekening], "Broek") = 13 AND TRUE

Another issue with your query is that you have used a HAVING clause in a context where a WHERE clause should be used, though I doubt this explains the error. A HAVING clause is applied to a criterion which follows the grouping of rows, and is usually applied to the result of an aggregation operation, e.g.

GROUP BY CustomerID
HAVING SUM(Amount) > 10000

to return rows where a customer's total purchases have exceeded 10,000 GBP for instance. In your case you are restricting rows before grouping so a WHERE clause should be used, positioning this before the GROUP BY clause. The criterion for the WHERE clause would be the same as you have used in the HAVING clause.

The inappropriate use of a HAVING clause usually results from the criterion having been entered in query design view in a column where GROUP BY has been selected in the Total row. The correct way to do it in design view is to add the field again to a second column in the design grid, uncheck the Show checkbox, select WHERE in the Total row, and enter the criterion in that column's Criteria row. In most cases the results will be the same, however, but nevertheless, if there's a wrong way and a right way I'd always use the latter.

Hello Pieter van Leeuwen,

Thank you for reaching out to Microsoft Q&A regarding the "Type Mismatch" error in your MS Access query. Your original query encounters a "Type Mismatch" error in the HAVING clause, specifically with the condition (((1 * InStrRev([Query21].[Naam tegenrekening], "Broek")) = 13)). Despite the expression evaluating to a Long Integer (VarType 3), the error persists. However, adding an extra condition like AND ((mutations.grootboek) = 4120) to the HAVING clause resolves it.

This error likely stems from a data type incompatibility in the join between [Expense Codes].GrtB (a numeric field) and mutations.grootboek (a text field). Access attempts to implicitly convert text values in grootboek to numbers during the join. If any rows contain non-numeric data (e.g., letters or invalid strings), the conversion fails, triggering the mismatch.

The additional condition in the second query allows Access's optimizer to filter out problematic rows early (effectively treating it like a WHERE clause), preventing invalid conversions and enabling the query to run. To fix this without relying on the extra condition:

1. Change the Data Type: If all values in mutations.grootboek are valid numbers, convert the field to a Number data type in your table design. This ensures consistent handling and avoids conversion issues.
2. Modify the Join with Explicit Conversion: Update the join in your query to use the Val() function, like this:

      ON [Expense Codes].GrtB = Val(mutations.grootboek)
   

   Note: Val() converts non-numeric strings to 0, which could cause unintended matches (e.g., matching invalid data to a GrtB value of 0). We recommend cleaning up any non-numeric data in grootboek first to prevent this.

You can also refer to Microsoft's documentation on data types and joins in Access for more details: https://support.microsoft.com/en-us/office/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6

hope this help

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.