Null input returns all values using wildcards

[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?

 

[Allen Browne]

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]...

 

[SwEdIsH_OfFiCe_UsEr]

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] & "*"));

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?

 

[ghetto_banjo]

Use the NZ function! Very easy.

Use this as paramter:

Like Nz([Value], "*")

 

[Allen Browne]

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] & "*"));

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?

 

[Allen Browne]

The problem with that is that it excludes any records where the *field* is
null.

 

[SwEdIsH_OfFiCe_UsEr]

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.

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] & "*"));

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?