Can You Figure Out What's Wrong W/ This Query

R

Rod

Can anyone figure out why this qurey does not select the following record:

SELECT tblCandidates.SOURCE, tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID, tblCandidates.DNC,
tblCandidates.Num_of_Calls, tblCandidates.[1st_Call],
tblCandidates.[2nd_Call], tblCandidates.[3rd_Call], tblCandidates.Last_Call,
tblCandidates.Archive, tblCandidates.MANAGER, tblCandidates.Booked,
tblCandidates.CANDIDATE, tblCandidates.CALL_RESULTS,
tblCandidates.INPUT_NUMBER, tblCandidates.FOLLOWUP_NUMBER,
tblCandidates.DATE_CONFIRMED, tblCandidates.Confirmation_Msg,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE
FROM tblCandidates
WHERE (((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)="-")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=1) AND ((tblCandidates.[2nd_Call])<=Date()) AND
((tblCandidates.Archive)>Date()) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))
ORDER BY tblCandidates.SOURCE DESC , tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID DESC;

ID: 79146
CANDIDATE: EDDIE
DNC: False
CALLED_ON: 4/23/2009
Num_of_Calls: 1
CALL_RESULTS: Message
PRIORITY: 1
1st_Call: 4/23/2009
2nd_Call: 6/22/2009
3rd_call: 8/21/2009
Last_Call: 10/20/2009
Archive: 11/19/2009
CO RESULTS: Null 'this field is empty

Note, I left out noting all of the other fields becuase they are not
examined in the query.

Thanks for your help!
 
D

Daryl S

The probleim is that your CO RESULTS field is null, and you have not provided
for that in your comparisons. Use the nz function in your comparisons, as
follows:

nz(tblCandidates.[CO RESULTS],"")<>"IBA"

--
Daryl S


Rod said:
Can anyone figure out why this qurey does not select the following record:

SELECT tblCandidates.SOURCE, tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID, tblCandidates.DNC,
tblCandidates.Num_of_Calls, tblCandidates.[1st_Call],
tblCandidates.[2nd_Call], tblCandidates.[3rd_Call], tblCandidates.Last_Call,
tblCandidates.Archive, tblCandidates.MANAGER, tblCandidates.Booked,
tblCandidates.CANDIDATE, tblCandidates.CALL_RESULTS,
tblCandidates.INPUT_NUMBER, tblCandidates.FOLLOWUP_NUMBER,
tblCandidates.DATE_CONFIRMED, tblCandidates.Confirmation_Msg,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE
FROM tblCandidates
WHERE (((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)="-")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=1) AND ((tblCandidates.[2nd_Call])<=Date()) AND
((tblCandidates.Archive)>Date()) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))
ORDER BY tblCandidates.SOURCE DESC , tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID DESC;

ID: 79146
CANDIDATE: EDDIE
DNC: False
CALLED_ON: 4/23/2009
Num_of_Calls: 1
CALL_RESULTS: Message
PRIORITY: 1
1st_Call: 4/23/2009
2nd_Call: 6/22/2009
3rd_call: 8/21/2009
Last_Call: 10/20/2009
Archive: 11/19/2009
CO RESULTS: Null 'this field is empty

Note, I left out noting all of the other fields becuase they are not
examined in the query.

Thanks for your help!
Click to expand...
 
R

Rod

In the design view it created a new field named: nz([tblCandidates].[CO
RESULTS],"") and the "SHOW" box is not selected. It moved the criteria that
was under CO RESULTS to this nz([tblCandidates].[CO RESULTS],"") field; is
that the way it is suppose to happen? The selection appears to be correct -
just trying to understand what happened and why.

Thanks MUCH!

Daryl S said:
The probleim is that your CO RESULTS field is null, and you have not provided
for that in your comparisons. Use the nz function in your comparisons, as
follows:

nz(tblCandidates.[CO RESULTS],"")<>"IBA"

--
Daryl S


Rod said:
Can anyone figure out why this qurey does not select the following record:

SELECT tblCandidates.SOURCE, tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID, tblCandidates.DNC,
tblCandidates.Num_of_Calls, tblCandidates.[1st_Call],
tblCandidates.[2nd_Call], tblCandidates.[3rd_Call], tblCandidates.Last_Call,
tblCandidates.Archive, tblCandidates.MANAGER, tblCandidates.Booked,
tblCandidates.CANDIDATE, tblCandidates.CALL_RESULTS,
tblCandidates.INPUT_NUMBER, tblCandidates.FOLLOWUP_NUMBER,
tblCandidates.DATE_CONFIRMED, tblCandidates.Confirmation_Msg,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE
FROM tblCandidates
WHERE (((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)="-")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=1) AND ((tblCandidates.[2nd_Call])<=Date()) AND
((tblCandidates.Archive)>Date()) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))
ORDER BY tblCandidates.SOURCE DESC , tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID DESC;

ID: 79146
CANDIDATE: EDDIE
DNC: False
CALLED_ON: 4/23/2009
Num_of_Calls: 1
CALL_RESULTS: Message
PRIORITY: 1
1st_Call: 4/23/2009
2nd_Call: 6/22/2009
3rd_call: 8/21/2009
Last_Call: 10/20/2009
Archive: 11/19/2009
CO RESULTS: Null 'this field is empty

Note, I left out noting all of the other fields becuase they are not
examined in the query.

Thanks for your help!
Click to expand...
Click to expand...
 
J

John Spencer

It appears that it doesn't meet any of the sets of criteria

Group 1 and Group 2: Num of Calls is not Zero
Group 3, 4, and 5 : [CO Results] is null and that fails the <> "IBA" test

You could try testing specifically for NULL as in
[CO Results] Is Null OR [CO Results] <> "IBA"

Or you could use
[CO Results] & "" <> "IBA"

Since I don't know how you want to handle a null value in this case you might
try something else

WHERE (tblCandidates.PRIORITY<3 AND tblCandidates.DNC=False AND
tblCandidates.Num_of_Calls=0 AND tblCandidates.CALL_RESULTS="-")
OR
(tblCandidates.PRIORITY<3 AND tblCandidates.DNC=False AND
tblCandidates.Num_of_Calls=0 AND tblCandidates.CALL_RESULTS<>"Bad
Number" And tblCandidates.CALL_RESULTS<>"Call Evenings")
OR
(tblCandidates.PRIORITY<3 AND tblCandidates.DNC=False AND
tblCandidates.Num_of_Calls=1 AND tblCandidates.[2nd_Call]<=Date() AND
tblCandidates.Archive>Date() AND (tblCandidates.CALL_RESULTS<>"Bad
Number" And tblCandidates.CALL_RESULTS<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA"))

OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))

OR ((tblCandidates.PRIORITY)<3 AND
(tblCandidates.DNC=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Can anyone figure out why this qurey does not select the following record:

SELECT tblCandidates.SOURCE, tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID, tblCandidates.DNC,
tblCandidates.Num_of_Calls, tblCandidates.[1st_Call],
tblCandidates.[2nd_Call], tblCandidates.[3rd_Call], tblCandidates.Last_Call,
tblCandidates.Archive, tblCandidates.MANAGER, tblCandidates.Booked,
tblCandidates.CANDIDATE, tblCandidates.CALL_RESULTS,
tblCandidates.INPUT_NUMBER, tblCandidates.FOLLOWUP_NUMBER,
tblCandidates.DATE_CONFIRMED, tblCandidates.Confirmation_Msg,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE
FROM tblCandidates
WHERE (((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)="-")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=1) AND ((tblCandidates.[2nd_Call])<=Date()) AND
((tblCandidates.Archive)>Date()) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))
ORDER BY tblCandidates.SOURCE DESC , tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID DESC;

ID: 79146
CANDIDATE: EDDIE
DNC: False
CALLED_ON: 4/23/2009
Num_of_Calls: 1
CALL_RESULTS: Message
PRIORITY: 1
1st_Call: 4/23/2009
2nd_Call: 6/22/2009
3rd_call: 8/21/2009
Last_Call: 10/20/2009
Archive: 11/19/2009
CO RESULTS: Null 'this field is empty

Note, I left out noting all of the other fields becuase they are not
examined in the query.

Thanks for your help!
Click to expand...
 
D

Daryl S

You can set the name of the field with the text and a colon before the
expression. For example, CO_RESULTS: nz([tblCandidates].[CO RESULTS],"").
Access sometimes splits the expression and criteria into two fields (this is
to make the SQL statement valid). If this is the case, the one with the
criteria would not be shown, while the one without the criteria would be
shown.




--
Daryl S


Rod said:
In the design view it created a new field named: nz([tblCandidates].[CO
RESULTS],"") and the "SHOW" box is not selected. It moved the criteria that
was under CO RESULTS to this nz([tblCandidates].[CO RESULTS],"") field; is
that the way it is suppose to happen? The selection appears to be correct -
just trying to understand what happened and why.

Thanks MUCH!

Daryl S said:
The probleim is that your CO RESULTS field is null, and you have not provided
for that in your comparisons. Use the nz function in your comparisons, as
follows:

nz(tblCandidates.[CO RESULTS],"")<>"IBA"

--
Daryl S


Rod said:
Can anyone figure out why this qurey does not select the following record:

SELECT tblCandidates.SOURCE, tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID, tblCandidates.DNC,
tblCandidates.Num_of_Calls, tblCandidates.[1st_Call],
tblCandidates.[2nd_Call], tblCandidates.[3rd_Call], tblCandidates.Last_Call,
tblCandidates.Archive, tblCandidates.MANAGER, tblCandidates.Booked,
tblCandidates.CANDIDATE, tblCandidates.CALL_RESULTS,
tblCandidates.INPUT_NUMBER, tblCandidates.FOLLOWUP_NUMBER,
tblCandidates.DATE_CONFIRMED, tblCandidates.Confirmation_Msg,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE
FROM tblCandidates
WHERE (((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)="-")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=0) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings")) OR
(((tblCandidates.PRIORITY)<3) AND ((tblCandidates.DNC)=False) AND
((tblCandidates.Num_of_Calls)=1) AND ((tblCandidates.[2nd_Call])<=Date()) AND
((tblCandidates.Archive)>Date()) AND ((tblCandidates.CALL_RESULTS)<>"Bad
Number" And (tblCandidates.CALL_RESULTS)<>"Call Evenings") AND
((tblCandidates.[CO RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=2) AND
((tblCandidates.[3rd_Call])<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA")) OR (((tblCandidates.PRIORITY)<3) AND
((tblCandidates.DNC)=False) AND ((tblCandidates.Num_of_Calls)=3) AND
((tblCandidates.Last_Call)<=Date()) AND ((tblCandidates.Archive)>Date()) AND
((tblCandidates.CALL_RESULTS)<>"Bad Number" And
(tblCandidates.CALL_RESULTS)<>"Call Evenings") AND ((tblCandidates.[CO
RESULTS])<>"IBA"))
ORDER BY tblCandidates.SOURCE DESC , tblCandidates.PRIORITY,
tblCandidates.CALLED_ON, tblCandidates.ID DESC;

ID: 79146
CANDIDATE: EDDIE
DNC: False
CALLED_ON: 4/23/2009
Num_of_Calls: 1
CALL_RESULTS: Message
PRIORITY: 1
1st_Call: 4/23/2009
2nd_Call: 6/22/2009
3rd_call: 8/21/2009
Last_Call: 10/20/2009
Archive: 11/19/2009
CO RESULTS: Null 'this field is empty

Note, I left out noting all of the other fields becuase they are not
examined in the query.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query ask two questions 1
Query criteria used twice in same query 4
Read-only form 1
Simple Update Q Problem 1
Can anyone help me figure this out? 3
Can Someone Help me figure this out? Not only finding multiparts 2
Can't Figure Out why this array is out of bounds now! 3
How To Get 1 Million Visitors Without Paying A Dime In Advertising 0

Top