Multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create the query to serve the multiple search criteria. I am
getting prolems with the null values. If I will enter values for search all
is working fine, but if I leave the criteria blank I am not getting the right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null) AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
 
It is just short addition to my original post. My problem is that when I
enter criteria for search I am getting not only the records matching criteria
but in addition records matching the null. How to eliminate those records,
but show them when no criteria was entered ???

Thanks for help.

Tony
 
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES) Like "*" & Nz([Forms]!!, "") & "*";

This assumes you want to match both DNO and DES values. If you want to match
either of them, replace the AND with OR.
 
If either DNO or DES might be Null then try
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

OR
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" OR
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

--
Duane Hookom
MS Access MVP


Duane Hookom said:
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES) Like "*" & Nz([Forms]!!, "") & "*";

This assumes you want to match both DNO and DES values. If you want to
match either of them, replace the AND with OR.
--
Duane Hookom
MS Access MVP


Tony said:
I am trying to create the query to serve the multiple search criteria. I
am
getting prolems with the null values. If I will enter values for search
all
is working fine, but if I leave the criteria blank I am not getting the
right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null) AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
Click to expand...
Click to expand...
 
Hi Duane,

Thank you very much for your response. But I am getting the following error
message when entering your suggested code:

Syntax error in query expression.

Any other suggestions. Thank you.

Tony



Duane Hookom said:
If either DNO or DES might be Null then try
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

OR
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" OR
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

--
Duane Hookom
MS Access MVP


Duane Hookom said:
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES) Like "*" & Nz([Forms]!!, "") & "*";

This assumes you want to match both DNO and DES values. If you want to
match either of them, replace the AND with OR.
--
Duane Hookom
MS Access MVP


Tony said:
I am trying to create the query to serve the multiple search criteria. I
am
getting prolems with the null values. If I will enter values for search
all
is working fine, but if I leave the criteria blank I am not getting the
right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null) AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
Click to expand...
Click to expand...
Click to expand...
 
I am trying to solve my problem. Doing that I was testing the IIf but I can
not have it right. I was thinking about using something like:

IIf([Forms]!![a] Is Null,,Like "*" & [Forms]!![a] & "*")

for my query criteria. But Access does not like this syntax :)

What I wanted is to ignore criteria if search key from my form is blank and
to set this search key as a query criteria if it is not blank.

Any suggestions ? Thank you.
 
Try:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE
( ( (D.DNO) Like "*" & [Forms]!![a] & "*") Or
( [Forms]!![a] Is Null) ) AND
( ( (D.DES) Like "*" & [Forms]!! & "*" ) Or
( [Forms]!! Is Null) );
 
Thank you very much for your help. It works exactly as I wanted it to be.

Tony

Van T. Dinh said:
Try:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE
( ( (D.DNO) Like "*" & [Forms]!![a] & "*") Or
( [Forms]!![a] Is Null) ) AND
( ( (D.DES) Like "*" & [Forms]!! & "*" ) Or
( [Forms]!! Is Null) );


--
HTH
Van T. Dinh
MVP (Access)




Tony said:
I am trying to create the query to serve the multiple search criteria. I am
getting prolems with the null values. If I will enter values for search all
is working fine, but if I leave the criteria blank I am not getting the right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null) AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
Click to expand...
Click to expand...
 
What is your exact SQL view?

--
Duane Hookom
MS Access MVP


Tony said:
Hi Duane,

Thank you very much for your response. But I am getting the following
error
message when entering your suggested code:

Syntax error in query expression.

Any other suggestions. Thank you.

Tony



Duane Hookom said:
If either DNO or DES might be Null then try
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

OR
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" OR
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

--
Duane Hookom
MS Access MVP


Duane Hookom said:
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES) Like "*" & Nz([Forms]!!, "") & "*";

This assumes you want to match both DNO and DES values. If you want to
match either of them, replace the AND with OR.
--
Duane Hookom
MS Access MVP


I am trying to create the query to serve the multiple search criteria.
I
am
getting prolems with the null values. If I will enter values for
search
all
is working fine, but if I leave the criteria blank I am not getting
the
right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null)
AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
Click to expand...
Click to expand...
Click to expand...
 
Get rid of all the dang ()s that Access puts in there:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE D.DNO & "" Like "*" & Nz([Forms]!![a], "") & "*" AND
D.DES & "" Like "*" & Nz([Forms]!!, "") & "*";

OR

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE D.DNO & "" Like "*" & Nz([Forms]!![a], "") & "*" OR
D.DES & "" Like "*" & Nz([Forms]!!, "") & "*";

--
Duane Hookom
MS Access MVP


Tony said:
Hi Duane,

Thank you very much for your response. But I am getting the following
error
message when entering your suggested code:

Syntax error in query expression.

Any other suggestions. Thank you.

Tony



Duane Hookom said:
If either DNO or DES might be Null then try
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

OR
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO & "") Like "*" & Nz([Forms]!![a], "") & "*" OR
((D.DES & "") Like "*" & Nz([Forms]!!, "") & "*";

--
Duane Hookom
MS Access MVP


Duane Hookom said:
SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & Nz([Forms]!![a], "") & "*" AND
((D.DES) Like "*" & Nz([Forms]!!, "") & "*";

This assumes you want to match both DNO and DES values. If you want to
match either of them, replace the AND with OR.
--
Duane Hookom
MS Access MVP


I am trying to create the query to serve the multiple search criteria.
I
am
getting prolems with the null values. If I will enter values for
search
all
is working fine, but if I leave the criteria blank I am not getting
the
right
results. My SQL code below:

SELECT D.DNO, D.DES, D.MAC, D.PL
FROM D LEFT JOIN R ON D.DNO = R.DRG
WHERE (((D.DNO) Like "*" & [Forms]!![a] & "*" Or (D.DNO) Is Null)
AND
((D.DES) Like "*" & [Forms]!! & "*" Or (D.DES) Is Null));

What I am doing wrong ?

Thanks for help.

Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top