Null input returns all values using wildcards

  • Thread starter Thread starter SwEdIsH_OfFiCe_UsEr
  • Start date Start date
S

SwEdIsH_OfFiCe_UsEr

How do I get all values in the searched area when I don't have any input in a
parameter?

I was looking for something like this:
IIf([Value] Is Null;"*";[Value])

But I read somewhere that it's not possible to use wildcards with
conditional expressions. Is that so, and how do I get around the problem?
 
Switch the query to SQL view, and craft the WHERE clause so it returns TRUE
when the parameter is null.

For example, if you currently have:
SELECT ...
FROM ...
WHERE SomeField Like "*" & [SomeParam] & "*"
then change the WHERE clause to:
WHERE (([SomeParam] Is Null) OR
(SomeField Like "*" & [SomeParam] & "*"))

Be careful with the bracketing if you mix ANDs and ORs.

If you have several criteria, this approach is both messy to maintain and
inefficient to execute. And it is subject to a bug:
http://allenbrowne.com/bug-13.html

A better solution is to build a filter string dynamically from controls on a
form. Here's a downloadable example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

message
news:[email protected]...
 
Thankz, that took care of that problem. But now I have another:
Two criterias from different columns interfere with one another. If I remove
one of them, the other one works, but when I have both of them, only the
second works. Why?


WHERE (((dateField) Between Nz([StartDate],#1/1/1900#) And
Nz([EndDate],#1/1/2100#)) AND ((someField) Is Null)) OR (((someField) Like
"*" & [someParam] & "*"));




Allen Browne said:
Switch the query to SQL view, and craft the WHERE clause so it returns TRUE
when the parameter is null.

For example, if you currently have:
SELECT ...
FROM ...
WHERE SomeField Like "*" & [SomeParam] & "*"
then change the WHERE clause to:
WHERE (([SomeParam] Is Null) OR
(SomeField Like "*" & [SomeParam] & "*"))

Be careful with the bracketing if you mix ANDs and ORs.

If you have several criteria, this approach is both messy to maintain and
inefficient to execute. And it is subject to a bug:
http://allenbrowne.com/bug-13.html

A better solution is to build a filter string dynamically from controls on a
form. Here's a downloadable example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

message
How do I get all values in the searched area when I don't have any
input in a parameter?

I was looking for something like this:
IIf([Value] Is Null;"*";[Value])

But I read somewhere that it's not possible to use wildcards with
conditional expressions. Is that so, and how do I get around the problem?
Click to expand...
Click to expand...
 
It's probably a matter of how you bracket this, because:
a AND (b OR c)
it not the same thing as:
(a AND b) OR c

The expression you have will only match if either of these conditions is
met:
a) the dateField is between the 2 dates and also somefield is null
b) someField is like the parameter with wildcards.

Removing the spurious brackets (Access adds them) it boils down to:
WHERE ((dateField Between Nz([StartDate],#1/1/1900#)
And Nz([EndDate],#1/1/2100#)) AND (someField Is Null))
OR (someField Like "*" & [someParam] & "*");

It won't returns records if someParam is null.
Nor where someField is not null but the dates don't match.

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

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

message
Thankz, that took care of that problem. But now I have another:
Two criterias from different columns interfere with one another. If I
remove
one of them, the other one works, but when I have both of them, only the
second works. Why?


WHERE (((dateField) Between Nz([StartDate],#1/1/1900#) And
Nz([EndDate],#1/1/2100#)) AND ((someField) Is Null)) OR (((someField) Like
"*" & [someParam] & "*"));




Allen Browne said:
Switch the query to SQL view, and craft the WHERE clause so it returns
TRUE
when the parameter is null.

For example, if you currently have:
SELECT ...
FROM ...
WHERE SomeField Like "*" & [SomeParam] & "*"
then change the WHERE clause to:
WHERE (([SomeParam] Is Null) OR
(SomeField Like "*" & [SomeParam] & "*"))

Be careful with the bracketing if you mix ANDs and ORs.

If you have several criteria, this approach is both messy to maintain and
inefficient to execute. And it is subject to a bug:
http://allenbrowne.com/bug-13.html

A better solution is to build a filter string dynamically from controls
on a
form. Here's a downloadable example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

in
message
How do I get all values in the searched area when I don't have any
input in a parameter?

I was looking for something like this:
IIf([Value] Is Null;"*";[Value])

But I read somewhere that it's not possible to use wildcards with
conditional expressions. Is that so, and how do I get around the
problem?
Click to expand...
Click to expand...
Click to expand...
 
Oh, thanks a mill! I'm kinda used to Excel so I didn't keep track of all the
brackets myself, but now I got it to work.

Allen Browne said:
It's probably a matter of how you bracket this, because:
a AND (b OR c)
it not the same thing as:
(a AND b) OR c

The expression you have will only match if either of these conditions is
met:
a) the dateField is between the 2 dates and also somefield is null
b) someField is like the parameter with wildcards.

Removing the spurious brackets (Access adds them) it boils down to:
WHERE ((dateField Between Nz([StartDate],#1/1/1900#)
And Nz([EndDate],#1/1/2100#)) AND (someField Is Null))
OR (someField Like "*" & [someParam] & "*");

It won't returns records if someParam is null.
Nor where someField is not null but the dates don't match.

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

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

message
Thankz, that took care of that problem. But now I have another:
Two criterias from different columns interfere with one another. If I
remove
one of them, the other one works, but when I have both of them, only the
second works. Why?


WHERE (((dateField) Between Nz([StartDate],#1/1/1900#) And
Nz([EndDate],#1/1/2100#)) AND ((someField) Is Null)) OR (((someField) Like
"*" & [someParam] & "*"));




Allen Browne said:
Switch the query to SQL view, and craft the WHERE clause so it returns
TRUE
when the parameter is null.

For example, if you currently have:
SELECT ...
FROM ...
WHERE SomeField Like "*" & [SomeParam] & "*"
then change the WHERE clause to:
WHERE (([SomeParam] Is Null) OR
(SomeField Like "*" & [SomeParam] & "*"))

Be careful with the bracketing if you mix ANDs and ORs.

If you have several criteria, this approach is both messy to maintain and
inefficient to execute. And it is subject to a bug:
http://allenbrowne.com/bug-13.html

A better solution is to build a filter string dynamically from controls
on a
form. Here's a downloadable example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

in
message
How do I get all values in the searched area when I don't have any
input in a parameter?

I was looking for something like this:
IIf([Value] Is Null;"*";[Value])

But I read somewhere that it's not possible to use wildcards with
conditional expressions. Is that so, and how do I get around the
problem?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top