how do InStr over two columns

[Guest]

I want the parameters of the query to check "skill1" column or "skill2"
column. I have it working for skill 1, what is the or part?

SELECT [FirstName1] & " " & [LastName1] & " " & [FirstName2] & " " &
[LastName2] AS Name, MEMBER_SKILL.MemberSkill1, MEMBER_SKILL.MemberSkill2,
MEMBER.DayPhone1, MEMBER.EvePhone1, MEMBER.DayPhone2, MEMBER.EvePhone2,
InStr("," & [Type Member Skill Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",") AS Expr1
FROM MEMBER INNER JOIN MEMBER_SKILL ON MEMBER.MemberID = MEMBER_SKILL.MemberID
WHERE (((InStr("," & [Type Member Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ","))>0));

Thanks

 

[Ken Snell [MVP]]

Just add a second criterion expression that uses the exact same parameter
(I've shown it with an AND logic, you may want to change it to OR logic):

SELECT [FirstName1] & " " & [LastName1] & " " & [FirstName2] & " " &
[LastName2] AS Name, MEMBER_SKILL.MemberSkill1, MEMBER_SKILL.MemberSkill2,
MEMBER.DayPhone1, MEMBER.EvePhone1, MEMBER.DayPhone2, MEMBER.EvePhone2,
InStr("," & [Type Member Skill Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",") AS Expr1
FROM MEMBER INNER JOIN MEMBER_SKILL ON MEMBER.MemberID =
MEMBER_SKILL.MemberID
WHERE InStr("," & [Type Member Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",")>0 AND InStr("," & [Type Member Numbers
separated by commas,Blank=All] &
",","," & [MemberSkill2] & ",")>0;

 

[Guest]

Thanks Ken. I thought I tried that by adding the Instr expr. to a second
column and moved the >0 to the "or" row, but it didn't work. (We newbies do
this all through design view, not str8 sql.)

Thanks for your help. Someday I owe you, John V., and the others lunch.

Just add a second criterion expression that uses the exact same parameter
(I've shown it with an AND logic, you may want to change it to OR logic):

SELECT [FirstName1] & " " & [LastName1] & " " & [FirstName2] & " " &
[LastName2] AS Name, MEMBER_SKILL.MemberSkill1, MEMBER_SKILL.MemberSkill2,
MEMBER.DayPhone1, MEMBER.EvePhone1, MEMBER.DayPhone2, MEMBER.EvePhone2,
InStr("," & [Type Member Skill Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",") AS Expr1
FROM MEMBER INNER JOIN MEMBER_SKILL ON MEMBER.MemberID =
MEMBER_SKILL.MemberID
WHERE InStr("," & [Type Member Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",")>0 AND InStr("," & [Type Member Numbers
separated by commas,Blank=All] &
",","," & [MemberSkill2] & ",")>0;



--

Ken Snell
<MS ACCESS MVP>

I want the parameters of the query to check "skill1" column or "skill2"
column. I have it working for skill 1, what is the or part?

SELECT [FirstName1] & " " & [LastName1] & " " & [FirstName2] & " " &
[LastName2] AS Name, MEMBER_SKILL.MemberSkill1, MEMBER_SKILL.MemberSkill2,
MEMBER.DayPhone1, MEMBER.EvePhone1, MEMBER.DayPhone2, MEMBER.EvePhone2,
InStr("," & [Type Member Skill Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ",") AS Expr1
FROM MEMBER INNER JOIN MEMBER_SKILL ON MEMBER.MemberID =
MEMBER_SKILL.MemberID
WHERE (((InStr("," & [Type Member Numbers separated by commas,Blank=All] &
",","," & [MemberSkill1] & ","))>0));

Thanks