error 94 invalid use of null

[hvsummer]

Hi everyone,
I want to find a string on another string, then i google code and copy it. After that i modify to suit my situation like this:

Public Function IsInArray(stringtocompare As Variant, valueToFind As Variant) As Boolean
On Error GoTo Err_F
' checks if valueToFind is found in arr, no loop!
If IsNull(stringtocompare) = False Then
    IsInArray = False
Else
    If stringtocompare = valueToFind Then
        IsInArray = True
    Else
        If InStr(1, stringtocompare, ",", vbTextCompare) = 0 Then
            IsInArray = False
        Else
            arrtocompare = Split(stringtocompare, ",")
            IsInArray = (UBound(Filter(arrtocompare, valueToFind)) > -1)
        End If
    End If
End If
Exit_F:
Exit Function
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Function
 

and when i run it inside an IIF function in expression of a query, the error "94invalid use of null" keep appear...

What is my problem ? how can I fix this ?
please help me,
thank you.

[NeoPa]

I suspect line #4 should read :

If IsNull(stringtocompare) Then

[hvsummer]

well, thank NeoPa, but that's not correct, I figure out that link 4 incorrect, it should be "=True" to filter the null value out of formula behind it... still thank you.

[NeoPa]

That seems to be a misunderstanding. Unless you're saying something different from what I understand you to be saying of course. It's not very clear as you've only put in a part of the line we're talking about.

If you mean that it should not be :

If IsNull(stringtocompare) Then

but should be :

If IsNull(stringtocompare) = True Then

instead, then I'd challenge you to test out your theory. From my perspective, and I have a certain amount of experience with such things, they would both give exactly the same result. The returned value of an IsNull() call can never be Null of course. The only difference being that the latter is a little more clumsy and says more than is required.

[hvsummer]

it did work mate, when the code run, first it filter out any null value as False value. then if field is not null, it run the code below it.

I have to code like that because not every stringtocompare field have value, some field is null, and it'll broken the code inside the 2nd "If".

and to introduce my code's target, it work like this

you set a field to compare that content some data (the ID that apply promote for example -ID promotion) like this "1,2,3,4,5,6,9" and some row are null, some row are 2,3,5 ; even a row with only 1 number "5"
now you have the ID on another table, and you want to check whether that ID have promotion or not, use my function
[

IsInArray(ID, ID promotion)
or
iif(IsInArray(ID, ID promotion)=True, 1, 0) to count
 

it'll give you the result very clearly, I think people searching for this code very much on internet, so It'll be helpfull here.

full code:

Public Function IsInArray(stringtocompare As Variant, valueToFind As Variant) As Boolean
On Error GoTo Err_F
' checks if valueToFind is found in arr, no loop!
If IsNull(stringtocompare) = True Then
    IsInArray = False
Else
    If stringtocompare = valueToFind Then
        IsInArray = True
    Else
        If InStr(1, stringtocompare, ",", vbTextCompare) = 0 Then
            IsInArray = False
        Else
            arrtocompare = Split(stringtocompare, ",")
            IsInArray = (UBound(Filter(arrtocompare, valueToFind)) > -1)
        End If
    End If
End If
Exit_F:
Exit Function
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Function

[NeoPa]

There seems to be some confusion here.

I never said that your code wouldn't work. In fact, I stated quite clearly that, though it may be more clumsy than what I'd suggested, both would work in almost identical fashion.

My post was submitted because in your post #3 you stated that my suggestion wouldn't work. That is simply not the case. I would suggest that you be a little more careful how you express yourself publicly and be more precise in your claims.

If I need to repeat myself then I will. Both examples of code should work, however your code is less precise and indicates a lack of clear understanding of what's needed to work with boolean values (As opposed to an understanding one can get away with).

I don't say that to belittle you, as I understand better than most that working with Booleans is an area that almost everyone struggles with. Nevertheless, I think you should be more careful with such bold statements unless you have a much better understanding of what it is you're stating.