DoCmd.OpenReport Filter Question

[booismycat]

I have a form that uses an option group (radio buttons) which allows the user to select a specific filter for a report.
The default is ALL records using the following:

DoCmd.OpenReport "ChecksByCategory", acViewReport

When the user selects something other than ALL, my code will convert the option value of the radio button to a string that I use as the option label.

DoCmd.OpenReport "ChecksByCategory", acViewReport, , "Category = " & myFilter




My problem: One option is to report on all records where Category is null and I can't get this to work. No matter how I code this, when Access does not error on my syntax (for is null), I get a blank report. All other options work. I have tried every variation of [Category] is Null that I can think of with no luck to date.
To work around this, I duplicated ChecksByCategory, modified the query to [Category] is null and named the report ChecksNoCategory.

How can I get my DoCmd.OpenReport to recognize "Is Null"?

Thank you

[lfpm062010]

One way,

IF ISNULL(Me!myFilter) THEN
DoCmd.OpenReport "ChecksByCategory", acViewReport
ELSE
DoCmd.OpenReport "ChecksByCategory", acViewReport, , "Category = " & myFilter
END IF

[booismycat]

Not sure you understand my issue as this is what I have tried and it does not resolve the issue.
When myFilter = "Is Null" or myFilter = "Not Is Null" the results are a blank report, which is not correct.

I guess my question is: How would I set myFilter to equal <Is Null> or <Not Is Null> without it being seen as a String because this seems to be what is happening.

[ranman256]

Things are not equal to null ( myFilter = "Is Null")
it has to be in the function IsNull([field])

SOMETIMES you can get results by using the empty set: [myField] = "" (instead of using the NULL)

[booismycat]

Yeah, I'm aware that "Is Null" is a string... and not the condition I need.
I still believe I should be able to set myFilter to Is Null so that - DoCmd.OpenReport "ChecksByCategory", acViewReport, , "Category = " & myFilter works properly.
WHERE ((Category) is null) - IS a valid SQL statement within MSAccess.

[booismycat]

I resolved my problem.

First, I set my function to be a variant
Function CategoryFilter() As Variant

This allowed me to eliminate the quotes that were seen as defining a string.
I then had to create an IF statement because the valid SQL WHERE statement in the docmd.openreport statement does not like the = sign when using the Is Null or/and the Not Is Null.

Thanks for the input.