Data Mismatch for UDF called from Where clause

[rouny4u]

Hello,

I started working on VBA,access from yesterday, and here I am with my first problem :). Please help.

I get the above mentioned problem when my UDF is returning an integer.(Following is the UDF):
**************************************************

Function Missing(a As String) As Integer
Static count_mis As Integer
On Error GoTo Err_Trap
 
 If a = "M" Then
  count_mis = 1
 ElseIf a = "I" Then
  count_mis = 2
 ElseIf IsNull(a) = True Then
  count_mis = 3
 ElseIf IsEmpty(a) = True Then
  count_mis = 4
 ElseIf IsError(a) = True Then
  count_mis = 5
 Else
  count_mis = 6
  End If
 
  Missing = count_mis
 
Exit_Trap:
   Exit Function
Err_Trap:
   MsgBox Err.Description
   Missing = 2
   Resume Exit_Trap
End Function
************************************************

The argument passed to the UDF is a field which is either I or M or blank. The call to the UDF in the Where clause is:
(Missing([Missing data summary].Field40) = 1)

But I get data mismatch here. Is it beacause of the blank values in Field40,( which show an error in the retrieved table,if I run the query withouth this criteria in where clause). I get a 1 and 2 respectively corresponding to "M" or "I" in the Field40

Thank you!

[rouny4u]

Sorry Forgot to mention,I need only those records to be selected which have a "M" in Field40 of Missing data summary table. I have written this complex logic as I need to further modify it such that the return value from the UDF will be derived from a logic on values of 30 fields in the record including field40. Thanks!!

[OldBirdman]

Try:

(Missing([Missing data summary].Field40)) = 1

The outer parens () are redundant, but might be needed when you expand to 30 fields.

[rouny4u]

@OldBirdman
@OldBirdman

Thank you so much for the prompt reply. The mismatch is gone but I get an Unknown Jet error now. I do not know what that is,so will get back once i know. Thanks

[rouny4u]

well, not sure what happened, but without changing much (or anything) I am back to the data mismatch error now. This is after i included the redundant parenthesis :) does this give us any clue?

[OldBirdman]

Not really!
The function should be "Public" and in a Standard Module, not a Class Module.
Can you post the SQL statement?

[rouny4u]

Yepp it is in standard module and have made it public now. same result though. Here is the SQL query(smaller version,all that is required now and goes down with same error):

SELECT [Missing data summary].Field40
FROM [Missing data summary]
WHERE((Missing([Missing data summary].Field40)) Like 1);

[OldBirdman]

What immediately stands out is that there is no blank after the keyword WHERE
If that is not the problem, open a new query in Design View. Close the "Show Table" window without using it. Click SQL on the query toolbar (leftmost icon). Paste your SQL statement into the textbox
Switch to DataSheet View (leftmost icon).
If it works, the problem is elsewhere in your code.
If it doesn't work, put a BreakPoint in your function "Missing" on the line [If a = "M" Then]. Step through the code to be sure it is working as you expect.
Switch to Design View for the query. This should have your function on the "Criteria:" row of the design grid.

I must leave now for a couple of hours. Good luck!

[rouny4u]

Hi, Thank you for you reply again. I tried out what you told me,the query does not work either way. I am unable to figure out what is wrong using breakpoints. However I have broken down the problem to the following:

There are 3 possible values of the field to be passed to
the function they are "m", "i" (both as String) and a NUll. The null causes the function to error, and even the trap i have set does not catch the error.

I tried to get around this by using a subquery in the FROM statement to filter out non-Null values only to which the where clause will be applied. If I do not use the Where clause the query works fine, but witht the where clause i again get data mismatch error.

Not sure why, when the values passed is an integer and I have an integer in the criteria condition.Following are the simplified query and code.

code:
Public Function Missing(Optional a As String) As Integer
On Error GoTo Err_Trap
Dim count_mis As Integer

If a = "M" Then
count_mis = 1
ElseIf a = "I" Then
count_mis = 2
Else: IsNull (a)
count_mis = 6
End If

Missing = count_mis

Exit_Trap:
Exit Function

Err_Trap:
MsgBox Err.Description
Missing = 2
Resume Exit_Trap

End Function

Query:
SELECT [Missing data summary].Field40, Missing([Missing data summary].Field40)
FROM (SELECT [Missing data summary].Field40 FROM [Missing data summary] WHERE [Missing data summary].Field40 IS Not Null)
WHERE (Missing([Missing data summary].Field40) = 1);

Thanks!!

[rouny4u]

Thank you very much. I got this. The Data Mismatch wasnt in the return value passed back to the query but the parameter passed by the query. Just had to accept the parameter in the UDF as a variant data type.

[OldBirdman]

I'm glad you figured it out yourself. Your complete statement of what you tried, and what happened was quite clear. Sometimes a carefully composed question can help solve a problem better than any help a forum can give.