Query criteria used twice in same query

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

I have the following query:

SELECT tblCandidates.CANDIDATE, tblCandidates.INPUT_NUMBER,
tblCandidates.CALLED_ON, tblCandidates.MANAGER, tblCandidates.DNC,
tblCandidates.ID, tblCandidates.CALL_RESULTS, tblCandidates.SOURCE,
tblCandidates.FOLLOWUP_NUMBER, tblCandidates.Booked, [AREA_CODE] &
[INPUT_NUMBER] AS [NUMBER], tblCandidates.DATE_CONFIRMED,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.PRIORITY, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE,
tblCandidates.[1st_Call], tblCandidates.[2nd_Call], tblCandidates.[3rd_Call],
tblCandidates.Last_Call, tblCandidates.Archive, tblCandidates.[Date of 1st No
Show Msg], tblCandidates.Num_of_Calls
FROM tblCandidates
WHERE (((tblCandidates.CANDIDATE) Like "*" & [Beginning of Candidate's Name]
& "*") AND ((tblCandidates.INPUT_NUMBER) Like [7 digit Phone Number] & "*")
AND ((tblCandidates.DNC)=False))
ORDER BY tblCandidates.CALLED_ON DESC;

This query needs to be modified, so when the user is asked to enter the "7
digit phone number" the query will check an additional field. I need it to
also check if the phone number entered is in field FOLLOWUP_NUMBER. If it is
in either field I need it to be returned as it currently does if it is found
in INPUT_NUMBER. So the input of the number needs to be used in two places
in the same query.

Thanks

Rod
 
This query needs to be modified, so when the user is asked to enter the "7
digit phone number" the query will check an additional field. I need it to
also check if the phone number entered is in field FOLLOWUP_NUMBER. If it is
in either field I need it to be returned as it currently does if it is found
in INPUT_NUMBER. So the input of the number needs to be used in two places
in the same query.
Click to expand...

In the grid, just put the same criterion on the *second* Criteria line under
FOLLOWUP_NUMBER. The SQL would be (getting rid of Access' extra parentheses
but putting in a very important new pair):

SELECT tblCandidates.CANDIDATE, tblCandidates.INPUT_NUMBER,
tblCandidates.CALLED_ON, tblCandidates.MANAGER, tblCandidates.DNC,
tblCandidates.ID, tblCandidates.CALL_RESULTS, tblCandidates.SOURCE,
tblCandidates.FOLLOWUP_NUMBER, tblCandidates.Booked, [AREA_CODE] &
[INPUT_NUMBER] AS [NUMBER], tblCandidates.DATE_CONFIRMED,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.PRIORITY, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE,
tblCandidates.[1st_Call], tblCandidates.[2nd_Call], tblCandidates.[3rd_Call],
tblCandidates.Last_Call, tblCandidates.Archive, tblCandidates.[Date of 1st No
Show Msg], tblCandidates.Num_of_Calls
FROM tblCandidates
WHERE
tblCandidates.CANDIDATE Like "*" & [Beginning of Candidate's Name] & "*"
AND (tblCandidates.INPUT_NUMBER Like [7 digit Phone Number] & "*"
OR tblCandidates.FOLLOWUP_NUMBER Like [7 digit Phone Number] & "*")
AND tblCandidates.DNC=False
ORDER BY tblCandidates.CALLED_ON DESC;
 
It turns out the area code is stored with FOLLOWUP_NUMBER but not with
INPUT_NUMBER. How can I just look at the right 7 characters of
FOLLOWUP_NUMBER. I tried Right([FOLLOWUP_NUMBER],7) in the field name but it
sees that as an expression. Right(Like [7 digit Phone Number] & "*",7)
doesn't work at all.

Thanks

John W. Vinson said:
This query needs to be modified, so when the user is asked to enter the "7
digit phone number" the query will check an additional field. I need it to
also check if the phone number entered is in field FOLLOWUP_NUMBER. If it is
in either field I need it to be returned as it currently does if it is found
in INPUT_NUMBER. So the input of the number needs to be used in two places
in the same query.
Click to expand...

In the grid, just put the same criterion on the *second* Criteria line under
FOLLOWUP_NUMBER. The SQL would be (getting rid of Access' extra parentheses
but putting in a very important new pair):

SELECT tblCandidates.CANDIDATE, tblCandidates.INPUT_NUMBER,
tblCandidates.CALLED_ON, tblCandidates.MANAGER, tblCandidates.DNC,
tblCandidates.ID, tblCandidates.CALL_RESULTS, tblCandidates.SOURCE,
tblCandidates.FOLLOWUP_NUMBER, tblCandidates.Booked, [AREA_CODE] &
[INPUT_NUMBER] AS [NUMBER], tblCandidates.DATE_CONFIRMED,
tblCandidates.AREA_CODE, tblCandidates.CONFIRMATION_DATE, "1" & [NUMBER] AS
DIAL_NUMBER, tblCandidates.CORP_OVERVIEW, tblCandidates.RETURNED_CALL,
tblCandidates.COMMENTS, tblCandidates.PRIORITY, tblCandidates.ATTENDED,
tblCandidates.[NO_SHOW_FOLLOW-UP], tblCandidates.[CO RESULTS],
tblCandidates.[Follow-up_Needed], tblCandidates.IBA_DATE,
tblCandidates.[1st_Call], tblCandidates.[2nd_Call], tblCandidates.[3rd_Call],
tblCandidates.Last_Call, tblCandidates.Archive, tblCandidates.[Date of 1st No
Show Msg], tblCandidates.Num_of_Calls
FROM tblCandidates
WHERE
tblCandidates.CANDIDATE Like "*" & [Beginning of Candidate's Name] & "*"
AND (tblCandidates.INPUT_NUMBER Like [7 digit Phone Number] & "*"
OR tblCandidates.FOLLOWUP_NUMBER Like [7 digit Phone Number] & "*")
AND tblCandidates.DNC=False
ORDER BY tblCandidates.CALLED_ON DESC;
Click to expand...
 
It turns out the area code is stored with FOLLOWUP_NUMBER but not with
INPUT_NUMBER. How can I just look at the right 7 characters of
FOLLOWUP_NUMBER. I tried Right([FOLLOWUP_NUMBER],7) in the field name but it
sees that as an expression. Right(Like [7 digit Phone Number] & "*",7)
doesn't work at all.
Click to expand...

Try

LIKE "*" & [7 digit Phone Number] & "*"

or - a bit safer -

LIKE "###" & [7 digit phone number] & "*"

assuming that the number is stored in the format

8005555555

If you have punctuation in the FOLLOWUP_NUMBER field you'll need to include it
in the criterion.
 
Perfect!

Thanks

John W. Vinson said:
It turns out the area code is stored with FOLLOWUP_NUMBER but not with
INPUT_NUMBER. How can I just look at the right 7 characters of
FOLLOWUP_NUMBER. I tried Right([FOLLOWUP_NUMBER],7) in the field name but it
sees that as an expression. Right(Like [7 digit Phone Number] & "*",7)
doesn't work at all.
Click to expand...

Try

LIKE "*" & [7 digit Phone Number] & "*"

or - a bit safer -

LIKE "###" & [7 digit phone number] & "*"

assuming that the number is stored in the format

8005555555

If you have punctuation in the FOLLOWUP_NUMBER field you'll need to include it
in the criterion.
Click to expand...
 
Back
Top