Datatype mismatch problem when using user-defined function in select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me to solve the following problem

I have written a user-defined function which returns a value in string type

When I use this function as an expression (one of the columns) in the query design grid, the select query result works fine and displays the result as expected (contain no null values). But when I add a criterion under the expression containing the user-defined function expression in the design grid (e.g. = "HQ"), datatype mismatch is prompted when the query is run

How can I workaround this

Thanks.
 
Can anyone help me to solve the following problem?

I have written a user-defined function which returns a value in string type.

When I use this function as an expression (one of the columns) in the query design grid, the select query result works fine and displays the result as expected (contain no null values). But when I add a criterion under the expression containing the user-defined function expression in the design grid (e.g. = "HQ"), datatype mismatch is prompted when the query is run.

How can I workaround this?

Thanks.
Click to expand...

Please post the SQL of this query. I'm not at all sure what your query
IS!
 
My SQL query which has been simplified to highlight the problem is given as follows

SELECT TryData.HKIC As HKIC, TryData.Name As Name, RespRegion([Qry Personal_N_Appts].CC1,[Qry Personal_N_Appts].GRADE) AS Resp_Region, [Qry Personal_N_Appts].GRADE,
FROM [Qry Personal_N_Appts] INNER JOIN TryData ON [Qry Personal_N_Appts].tblBasic_Personal.HKID=TryData.HKI
WHERE (RespRegion([Qry Personal_N_Appts].CC1,[Qry Personal_N_Appts].GRADE)="HQ"
ORDER BY RespRegion([Qry Personal_N_Appts].CC1,[Qry Personal_N_Appts].GRADE)

RespRegion is a user-defined function

Function RespRegion(strCC As String, strGrade As String) As Strin
On Error GoTo ErrorHandl
RespRegion = DLookup("[Region]", "[lookuptblCost_Centre_Institution]", "[Cost_Centre_Institution_Code]= '" & Mid(Nz(strCC), 1, 2) & "'"
GoTo HQ_Conditio
ErrorHandle
RespRegion = "Unknown
HQ_Condition
If (Nz(strGrade) = "M&HO" Or strGrade = "DO" Or strGrade = "EO") The
RespRegion = "HQ
End I
End Functio

The above select query works fine without the WHERE clause. But, when the WHERE clause is added, datatype mismatch error in criteria expression is prompted
 
Back
Top