Results 1 to 2 of 2

Thread: Use Instr

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Use Instr

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I'd just stick to LIKE. Either:
    Code:
    Val(Left([LastValidTech].[Account1], 5)) LIKE "*" & tech_id.corp & "*"
    OR
    Code:
    tech_id.corp LIKE "*" & Val(Left([LastValidTech].[Account1], 5)) & "*"
    I'm not certain what your error is below but it could be that you have the arguments back to front. Which value do you want to find in which field?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •