Function return value to Access query

I'm returning an Integer from a VBA function in a global module to a query in Access 2000. That's fine. But as soon as I try to give the returning Integer a criteria like >0 or <>0, the resulting DataSheet produces the results of the query (judging from the first 20 or so rows, successfully) then an error message box saying that there is a data type mismatch. As soon as I OK the message box, Access redraws the datasheet with all errors. I've tried returning a Variant from the function but still had the same problem.

I've tried a search on this forum and a few Access and VB books but can't find an answer. Any help? Thanks.

The relevant portion of the SQL WHERE clause is
((CalcMaterial([TicketDetail].[Description]))<>0)

CalcMaterial is the VB function
Public Function CalcMaterial(strDescription As String) As Integer

As I said, the query has no problem with the returned value as long as there is no criteria against it.

Steve

This sounds as though the field you are testing against has some illegal entries in some of the records (at least 1 record) probably a NULL value.

That was it Bill. Without criteria, there is 1 entry far down in the datasheet. Thanks.

I still don't understand how the function returns a NULL. The default return value of 0 for the function is set as soon as the function starts. Maybe I need a few tips on how to better write a function. Here's the beginning...

Code:

---

Public Function CalcMaterial(strDescription As String) As Integer
On Error GoTo Err_CalcMaterial
    CalcMaterial = 0
    If IsNull(strDescription) Then
        Exit Function
    End If
    Dim strLen As Integer
    strLen = Len(strDescription)
    If strLen < 1 Then
        CalcMaterial = 0
        Exit Function
    End If

---

And the error handler at the end

Code:

---

Err_CalcMaterial:
    MsgBox "Err_CalcMaterial: " & Err.Description
    CalcMaterial = 0
End Function

---

Steve

A string is initialised to "", therefore when you pass it into the function, you should test for both conditions.

Code:

---

If (IsNull(strDescription) OR strDescription = "") Then

---

I thought the Len() fxn would check for that the same way strlen/_tcslen does in C. I needed the length for other parsing/string extraction in the function so I thought I might as well get it at the beginning and kill 2 birds with one stone.

Steve