Multiple criteria

[Tony]

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

 

[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

 

[Duane Hookom]

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]

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

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

 

[Tony]

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

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

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

 

[Tony]

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.

 

[Van T. Dinh]

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) );

 

[Tony]

Thank you very much for your help. It works exactly as I wanted it to be.

Tony

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)

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

 

[Duane Hookom]

What is your exact SQL view?

--
Duane Hookom
MS Access MVP

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

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

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

 

[Duane Hookom]

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

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

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

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