Access Crosstab Data type mismatch in criteria expression

Question

The select query works perfectly (access 2013). As soon as I change it to a crosstab it gives me the error "Data type mismatch in criteria expression." I am using a vba function in the crosstab value as an expression. I have no null values in my table and have tried using variant as my data type.

VBA

enter code here
'assumes that the rate is monthly
Public Function payFrequency(frequency As String, rate As Currency) As Currency
If frequency = "BI-WEEKLY" Then
    payFrequency = rate * 12 / 26
    Exit Function
ElseIf frequency = "SEMI-MONTHLY" Then
    payFrequency = rate * 12 / 24
    Exit Function
ElseIf frequency = "MONTHLY" Then
    payFrequency = rate
    Exit Function
Else
    payFrequency = 0
    Exit Function
End If
End Function

and the SQL

TRANSFORM 
  payfrequency([structurepayrollFrequency],
  Max([2015_PREMIUM]-[2015_EMPLOYER_CONTR])) AS 2015_EMPLOYEE_CONTR

I wonder what happens if, instead of VBA, you build this function in the query. That would be my preferred solution in any case; VBA calls from a query are often not efficient, and can be harder to trouble shoot. — Smandoli

Smandoli Smandoli · Accepted Answer · 2015-10-19T20:25:19

Instead of VBA, I suggest you build this function in the query. That would be my preferred solution in any case; VBA calls from a query are often not efficient, and can be harder to trouble shoot.

Access Crosstab Data type mismatch in criteria expression

1 Answers