Function return value to Access query

[sstofka]

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

[Bill Crawley]

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.

[sstofka]

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...

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

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

Steve

[Twodogs]

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

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

[sstofka]

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