Complex Expressions or VBA Functions as criteria in a Query

[hiero]

Hi there,

I have a list (listA) which has a record source of a query object (queryA).
The form (formA) which has listA in it, also has a text box for some of the fields requested in queryA. The goal is to provide a flexible filter for the records in the list, so the user can just type in 'K' in text box filter_name, and they will only see records matching the entry.

N0w, the scene is set! I can easily go into the query object, and in criteria type something like this:

Like "*" & [Forms]![formA]![filter_name] & "*"

This works! It flexible filters out the records matching the text box value..
But, if the record has NULL for the name, then it won't even match "**", which is the minimum criteria. So therefore I get exclusion of records, which I dont want.

So I thought perhaps there might be some control structure I can use in expressions... some if, else, end if statement? Does anyone know how to do this?

Alternatively I thought, I could write a function like this..

Function criteria(ByVal val As Variant) As Variant
If (IsNull(val) Or IsEmpty(val) Or val = "") Then
    'do nothing
Else
    criteria = "Like *" & val & "*"
End If
End Function

And then in the queryA object for criteria you just use:

=criteria(Forms!formA!filter_name)

But this doesn't seem to work.. and I dont know how to debug this...

Any ideas? I thought the function was a neat solution, but it seems not!

Thanks in advance!

Hiero

 

[hiero]

Fantastic, thanks Pat.
This works:

Like "*" & [forms]![itemForm]![filter_name] & "*" Or [forms]![itemForm]![filter_name] Is Null

I didn't see the direct connection with the sql query.
And yes - I need it so that partial results come up - much more useful.

Thanks again.