Use Instr

[Eric]

I try to use the instr method in my query. Purpose is if it exactly
match the LastValidTech.Account1 or match some digit with tech_id.corp
it shows the result. I make a mistake in Instr function can any one
please help me out.

WHERE InStr(Val(Left([LastValidTech].[Account1], 5)), tech_id.corp) > 0
or Val(Left([LastValidTech].[Account1], 5)) = [tech_id.corp]
----------------------------------------------------------------------------------------------------------------------



INSERT INTO TBL_DHGErrorReport ( TicketNum, MainAccnt, Equipment,
PPVVOD_Amt, OtherAccnt1, OtherAccnt2, OtherAccnt3, LstVldTech,
TechCont, Corp, RequestDate )
SELECT tbl_PPVResearch.TicketNum, LastValidTech.Account1,
tbl_Events.txt, tbl_Events.PPVVOD_Amt, LastValidTech.Account2,
LastValidTech.Account3, LastValidTech.Account4,
LastValidTech.LstVldTech, tech_id.TECHCONT, tech_id.CORP,
tbl_PPVResearch.RequestDate
FROM ((LastValidTech INNER JOIN tbl_PPVResearch ON
LastValidTech.TicketNum = tbl_PPVResearch.TicketNum) INNER JOIN
tbl_Events ON tbl_PPVResearch.TicketNum = tbl_Events.TicketNum) INNER
JOIN tech_id ON LastValidTech.LstVldTech = tech_id.TECH
WHERE (((tbl_PPVResearch.RequestDate) Between
[Forms]![frm_Admin]![text33] And [Forms]![frm_Admin]![text35]) AND
InStr(1, LastValidTech.Account1, tech_id.corp) > 0 or
Val(Left([LastValidTech].[Account1], 5)) = [tech_id.corp]


Thanks.

 

[KARL DEWEY]

You are first extracting the left 5 characters of Account1. You then convert
text to a numerical value. Then inside of that value you are looking for what
is contained in tech_id.corp.

Is this really what you entended to do?