NULL is design view criteria

[Guest]

I created a table [A] with a field [name] for persons name and a field
[closed] that contains the date the record was closed. I then created query
linked to this table in the design view and placed a combo box [cboName]
(from a form) in the criteria for [name]. Worked OK.

I wanted the query to return either 1) open records, 2) closed records, or
3) all records. So I created an option group with buttons Open, Closed, and
Both. I set a global variable [g_strStatus] = “Is Nullâ€, or “Is Not Nullâ€,
or “Is Null or Is Not Null†based on the result of the option group. I then
used a function [GetStatus()] to pass the value to the query. When I run the
query I get the “too complex†message.

If in the design view I type any of the choices, “Is Nullâ€, “Is Not Nullâ€,
or “Is Null or Is Not Null†in criteria for [closed] and then click the
DataSheet View, everything works as expected.

Sooo…

1. What am I missing?

2. Is there an easier way?

 

[Tom Ellison]

Dear SJW:

What I am missing is the text of your query. If you posted that I might
have a clue.

There is an easier way than setting a global, writing a function, and
invoking that function. Assuming this is a Jet query, you can just
reference the value of any control on an open form:

[Forms]![FormName]![ControlName]

Replace the actual names of your form and control.

The global may have a slight advantage if there is any possibility that the
form may be closed. You cannot reference the control from the query if it
is closed, but the variable would "remember" what it was when it was open.

Tom Ellison

 

[Michel Walsh]

Hi,


Your function seems to return a string but at a level where SQL is not
reading a string but expecting an operator, a little bit like if I ask you
what is the result of:


3 "+" 4


if you read in my mind, you answer 7, but if you are dumb as a computer, you
see 3 arguments, two numbers and a string, no operator at all, and answer
there is some error or something you didn't expect to get.


try:

WHERE SWITCH( FORMS!FormName!Option=1, myFIeld IS NULL,
FORMS!FormName!Option = 2, MyField IS NOT NULL,
FORMS!FormName!Option = 3, true )


or better,

WHERE SWITCH( FORMS!FormName!Option=1, myField IS NULL,
FORMS!FormName!Option=2, MyField IS NOT NULL,
true, true )




so, if option = 1, myField Is Null is evaluated and if true, the WHERE
clause keep it; if option =2, the record is kept only if MyField IS NOT null
return true; otherwise (or only if Option = 3 in the first syntax), keep
the record (such as if no condition was applied).



Hoping it may help,
Vanderghast, Access MVP