Null is my enemy....

[CorporateQAinTX]

Try this one.


IIf (IsNull(variable), {Insert wildcard search here}, (variable))

If variable is null then I want search for all variable's in the table. Can
I do this with a general "*" wildcard? I keep trying, but it doesn't seem to
work.

Here is the actual code I'm trying to use.
IIf(IsNull([Forms]![f_HoldReport]![Location]),Like
"*",[Forms]![f_HoldReport]![Location])

Thanks for the help ahead of time.

 

[Dirk Goldgar]

Try this one.


IIf (IsNull(variable), {Insert wildcard search here}, (variable))

If variable is null then I want search for all variable's in the table.
Can
I do this with a general "*" wildcard? I keep trying, but it doesn't seem
to
work.

Here is the actual code I'm trying to use.
IIf(IsNull([Forms]![f_HoldReport]![Location]),Like
"*",[Forms]![f_HoldReport]![Location])

Thanks for the help ahead of time.

Is this for a criterion in a query? If so, maybe this is what you want:

=variable Or (variable Is Null)

Or else maybe this:

Like IIf(variable Is Null, "*", variable)

But your talk of "variables" in the table is confusing me, and I'm not sure
of the context of what you're doing and what you really want.

 

[Allen Browne]

This one catches many people. The fact is that Null doesn't match anything:
not another null, nor a wildcard.

The solution is to craft the WHERE condition of your query so that it
returns True for all records instead of comparing the value to Null at all.
You do this by switching the query to SQL View, locating the WHERE clause,
and editing it like this:
WHERE (([Forms]![f_HoldReport]![Location] Is Null) OR
([SomeField] Like "*" & [Forms]![f_HoldReport]![Location] & "*"))

If the Location text box is null, the expression:
[Forms]![f_HoldReport]![Location] Is Null
is true, and so the entire WHERE clause returns True. Since it is true
regardless of the value in the record, it is true for every record, and so
all records are returned.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[CorporateQAinTX]

Thank you very much. I've been using your website for the last couple of
weeks and, I've got to say, I've learned a lot. I wasn't quite understanding
the SQL part but I'm getting better. This definately answered the problem.

I've got one question though. Is there a limit to how many parameters can be
used in a single query? I'm looking at 12 parameters. After the 5th one I
started getting an error when I tried to run the query. "Query too complex".
Do I need to reduce the number of parameters or just split them up between
several other queries?

Thanks for your help....

This one catches many people. The fact is that Null doesn't match anything:
not another null, nor a wildcard.

The solution is to craft the WHERE condition of your query so that it
returns True for all records instead of comparing the value to Null at all.
You do this by switching the query to SQL View, locating the WHERE clause,
and editing it like this:
WHERE (([Forms]![f_HoldReport]![Location] Is Null) OR
([SomeField] Like "*" & [Forms]![f_HoldReport]![Location] & "*"))

If the Location text box is null, the expression:
[Forms]![f_HoldReport]![Location] Is Null
is true, and so the entire WHERE clause returns True. Since it is true
regardless of the value in the record, it is true for every record, and so
all records are returned.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Try this one.


IIf (IsNull(variable), {Insert wildcard search here}, (variable))

If variable is null then I want search for all variable's in the table.
Can
I do this with a general "*" wildcard? I keep trying, but it doesn't seem
to
work.

Here is the actual code I'm trying to use.
IIf(IsNull([Forms]![f_HoldReport]![Location]),Like
"*",[Forms]![f_HoldReport]![Location])

Thanks for the help ahead of time.

 

[Allen Browne]

Thank you very much. I've been using your website ...

I've got one question though. Is there a limit to how many parameters can
be
used in a single query? I'm looking at 12 parameters. After the 5th one I
started getting an error when I tried to run the query. "Query too
complex".
Do I need to reduce the number of parameters or just split them up between
several other queries?

Thank you for your comments.

I don't see a limit on the number of parameters in a query. 12 sounds
feasible, though from the user's point of view, answering 12 dialogs every
time you want to run the query sounds an awefully tedious way to interface
it. It might be better to provide a form where the user can make their
choices and even correct their entries before firing off the query.

'Too complex' is just Access' way of saying it doesn't understand the query.
There can be lots of reasons, e.g. malformed arguments, missing delimiters,
inappropriate data types, wrong bracketing, or using reserved words as field
names. That's more common than a query that is actually too complex (e.g.
more than 100 phrases in a WHERE clause.)

You may have already seen this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It explains how to build a form that offers heaps of criteria options, and
build an efficient filter that uses only the boxes where the user enters
some limiting values.

 

[John Spencer]

The query is getting too complex because the where clause is growing
exponentially (or approx)

If you have
(FieldA = [Parameter] or [Parameter] is null)
AND (FieldB = [ParameterB] or [ParameterB is Null)

Access will restructure that to
(FieldA = [Parameter] and FieldB = [ParameterB])
OR (FieldA = [Parameter] and [ParameterB] is Null)
OR ([Parameter] is Null and FieldB = [ParameterB])
OR ([Parameter] Is null and [ParameterB] is Null)

Add a third pair and the number of criteria lines grows to 8 (2^3)
Add a fourth pair and the number of lines grows to 16 (2^4)
Add a fifth pair and the number of lines grows to 32 (2^5)

12 pair would require 4,096 sets of criteria

For instance, here is a query with 4 pairs of criteria

PARAMETERS FI Short, FS Text ( 255 ), FL Text ( 255 ), FK Text ( 255 );
SELECT FAQ.fID, FAQ.fLink, FAQ.fSubject, FAQ.fKeywords
FROM FAQ
WHERE (((FAQ.fID)=[Fi]) AND ((FAQ.fLink)=[fl]) AND ((FAQ.fSubject)=[fs]) AND
((FAQ.fKeywords)=[Fk]))
OR (((FAQ.fLink)=[fl]) AND ((FAQ.fSubject)=[fs]) AND ((FAQ.fKeywords)=[Fk])
AND (([fi]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fSubject)=[fs]) AND ((FAQ.fKeywords)=[Fk]) AND
(([fl]) Is Null))
OR (((FAQ.fSubject)=[fs]) AND ((FAQ.fKeywords)=[Fk]) AND (([fi]) Is Null) AND
(([fl]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fLink)=[fl]) AND ((FAQ.fKeywords)=[Fk]) AND
(([fs]) Is Null))
OR (((FAQ.fLink)=[fl]) AND ((FAQ.fKeywords)=[Fk]) AND (([fi]) Is Null) AND
(([fs]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fKeywords)=[Fk]) AND (([fl]) Is Null) AND
(([fs]) Is Null))
OR (((FAQ.fKeywords)=[Fk]) AND (([fi]) Is Null) AND (([fl]) Is Null) AND
(([fs]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fLink)=[fl]) AND ((FAQ.fSubject)=[fs]) AND
(([fk]) Is Null))
OR (((FAQ.fLink)=[fl]) AND ((FAQ.fSubject)=[fs]) AND (([fi]) Is Null) AND
(([fk]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fSubject)=[fs]) AND (([fl]) Is Null) AND
(([fk]) Is Null))
OR (((FAQ.fSubject)=[fs]) AND (([fi]) Is Null) AND (([fl]) Is Null) AND
(([fk]) Is Null))
OR (((FAQ.fID)=[Fi]) AND ((FAQ.fLink)=[fl]) AND (([fs]) Is Null) AND (([fk])
Is Null)) OR (((FAQ.fLink)=[fl]) AND (([fi]) Is Null) AND (([fs]) Is Null) AND
(([fk]) Is Null))
OR (((FAQ.fID)=[Fi]) AND (([fl]) Is Null) AND (([fs]) Is Null) AND (([fk]) Is
Null))
OR ((([fi]) Is Null) AND (([fl]) Is Null) AND (([fs]) Is Null) AND (([fk]) Is
Null));



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County