Data type mismatch in query

[thart21]

Could someone please look at the below module and query and tell me if you know why I am getting a data type mismatch error when running?
Both fields in the table are "Text" so I don't get why it's doing this. Found this code on another site.

Module
Public Function Concat(strDEV_CD As String, _
strConstraint As String) As String
Static strLastDEV_CD As String
Static strConstraints As String



If strDEV_CD = strLastDEV_CD Then
strConstraints = strConstraints & ", " & strConstraint
Else
strLastDEV_CD = strDEV_CD
strConstraints = strConstraint
End If
Concat = strConstraints
End Function

Query
SELECT DEV_CD, Max(Concat(DEV_CD,MAN_CON_DESC)) AS Constraints
FROM [Copy Of q_Combined]
GROUP BY DEV_CD;

Thanks!

[weekend00]

I did not see any problem.

[pbaldy]

Could either value be Null?

[ajetrumpet]

and...what light is breaking?

[thart21]

yes, there will be null values in MAN_CON_DESC field.

Apparently I need to account for that in my query?

Thanks

[pbaldy]

Or in the function. This:

strConstraint As String

can only accept a String; it will error if a Null is passed to it. Only a Variant can accept a Null. So either you modify the function to handle Nulls or use the Nz() function in the query call to pass something other than Null to the function.

[thart21]

Don't know what I'm doing wrong but I'm getting an error message "Reserved error (-1038) there is no message for this error", when changing the query to read:

Man: Max(Concat(Nz([DEV_CD],"Null"),Nz([MAN_CON_DESC],"Null ")))

I've also tried
Man: Max(Concat([DEV_CD],Nz([MAN_CON_DESC],"Null"))

Same error

Thanks,

Toni

[pbaldy]

Have you set a breakpoint to see if it's getting through the function correctly?

[ajetrumpet]

Have you set a breakpoint to see if it's getting through the function correctly?

either that, or press F8 to run it line by line. If you'd like some quick tips on how to use the editor's debugging tools, have a look here: http://www.ajenterprisesonline.com/ab/_vbe/