Null Not Working in Query Search Criteria Variable

[Ronster]

I have a report that is linked to a query that searches for the letter
"A" or "B" in a field called CompanyType. I created a function of
Variant type that holds a variable of Variant type. The variable is
called MySelection. MySelection has 1 of 3 options, it can be "A"
or "C" or Null. "A" and "C" work great but when I want to
search for Null it doesn't work. If I type Is Null directly in query
it displays the few records with no values in CompanyType field, works
ok, but I can't seem to capture a Null value in the MySelection
variable.
Again, these work ok:
MySelection = "A"
MySelection = "C"

I'm using the following command to execute the report:
DoCmd.OpenReport "rptCompanyReport", A_PRINT

I have tried the following but none of these work:
MySelection = Null
MySelection = "Is Null"
MySelection = "<>""A"" or <>""C""
MySelection = "Not""A"" or Not""C""

I know I can create a separate query and/or report to work around this
but is there any way to capture a Null value or not display an "A"
or "B" in my variable?

 

[Jeff Boyce]

"Null" and a zero-length string ("") look the same, but behave differently.

If you open a query in design mode, add the table, add this field, and put
"Null" in the selection criterion under this field (Access changes it to "Is
Null"), then run the query, do you get the rows you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ronster]

"Null" and a zero-length string ("") look the same, but behave differently.

If you open a query in design mode, add the table, add this field, and put
"Null" in the selection criterion under this field (Access changes it to "Is
Null"), then run the query, do you get the rows you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes, I get the 3 records that do not have an "A" or "C" or anything
else in that field. I was thinking that rather than searches for Null
values it would be better if I search for values that are not "A" or
"C" but still wondering if I can seach Null using a variable.

 

[Jeff Boyce]

By the way, <>"A" AND <>"B", rather than <>"A" OR <> "B"

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ronster]

By the way, <>"A" AND <>"B", rather than <>"A" OR <> "B"

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, when I type in <>"A" AND <>"B" I get nothing. If I type <> "A" I
get all the B's and if I type <>"B" I get all the A's but <>"A" AND
<>"B" does not show me the 3 records with nothing in the field, only if
I type in Is Null will I see the 3 records but I still want to capture
the criteria parameter in a variable. By the way I am doing multiple
Joins in this query (6 tables), not sure if that affects things or not.
I'm a little new to Access.

 

[Jeff Boyce]

?perhaps you could try:
(<>"A" AND <> "B") OR IsNull

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ronster]

?perhaps you could try:
(<>"A" AND <> "B") OR IsNull

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff I couldn't take it any more. I just created a new query and
everything works great. Thanks for the help. I will try your above
when I get some breathing room.