Syntax for Like Operator with Function as Query Criteria 1

[xweyer]

I have a function "fProgram" attached to a global string variable (gblProgram) that I'm using as criteria in a query field ([Program]). This works fine when the variable is an exact match but what I'd like to be able to do is also show all records. If I try setting gblProgram as Null via VBA the code produces an "invalid use of Null" error.

So instead I'm using the code to pass an asterisk and attempting to build the criteria on query side using the like operator. I've tried many variations of single quotes, double quotes and chr(34) to try and get a working "Like fProgram()" aka (Like "*") string but without any luck.

(Access treats the function different than it would a field name in that some syntax that would work with a field fail with the function.)

Does anyone know the magic phrase to get this to work?

 

[PHV]

the code produces an "invalid use of Null" error
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xweyer]

gblProgram = Null

 

[PHV]

Why not posting all your stuff ?
How is declared gblProgram ?
What is the whole code (declaration and body) of fProgram ?
How do you code the criteria (query grid or SQL where clause) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LarrySteele]

I tossed together a simple function:

Public Function fProgram() As String
    fProgram= "*48*"
End Function

and used the following criteria in my query:
[tt]Like fProgram()[/tt]

This worked just fine and filtered out the correct records.

- Larry

 

[PHV]

"invalid use of Null" error
The key is to use variant:

Public gblProgram As Variant

Public Function fProgram() As Variant
    fProgram = gblProgram
End Function

And in the criteria cell of the query grid:

=fProgram() OR fProgram() Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xweyer]

Sorry, PHV. I Didn't mean to be overly brief. I thought you were asking specifically which line set gblProgram as Null and produced the error in vba.

Anyway you solved the problem. Setting the global variable and function as variants did the trick. My code for setting the variable and function was the same as yours except that I set them as strings.

BTW Like fProgram()&"*" also now works as criteria.

For the record on the VBA side the part I left out was this

Dim strInput As String
strInput = Inputbox("Enter the four digit Program Code or * for all"), "PROGRAM")

If IsNull(strInput) OR strInput = "" Then
DoCmd.CancelEvent
ElseIf strInput = "*" Then
gblProgram = Null
Else
gblProgram = strInput
EndIf

Thanks again.