Query criteria used twice in same query

[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

 

[John W. Vinson]

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.

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;

 

[Rod]

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

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.

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;

 

[John W. Vinson]

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.

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.

 

[Rod]

Perfect!

Thanks

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.

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.