locked
IIF Like and Not Like Or Or Or RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello fellow Access enthusiasts.  I am trying to re-write an Excel formula in Access and so far have not had good luck.  Here is the formula:

    =COUNTIFS(Tag,"*PC5*",Tag,"<>*APC5*",Tag,"<>*PC5A*",Tag,"<>*NPC5*",Line,"<>*GSOCLAIMS*",Queue,"<>*END*")

    The words TAG, Line and Queue are named ranges of columns, basically columns A,B and G respectively 

    Please help me.  Thank you :)

    Robert


    • Edited by greenthnkr Monday, September 21, 2015 6:54 PM
    Monday, September 21, 2015 6:48 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to resolve my problem by breaking up the code into two separate queries.  In my first query I entered this code to pick out a majority of the records I didn't need:

    prePC5: IIf([TAG_LINE_CMMT] Like "*PC5*" And [BUS_AREA] Not Like "*GSOCLAIMS*" And [QUEUECD] Not Like "*END*",1,0)

    Then in the second query I put this code to filter out the rest of the records I did not want:

    PC5: IIf([TAG_LINE_CMMT] Like "*PC5A*",0,IIf([TAG_LINE_CMMT] Like "*APC5*",0,[prePC5]))

    I should have mentioned in my original post that 

    Tag = [TAG_LINE_CMMT]

    Line = [BUS_AREA]

    Queue = [QUEUECD]

    Hopefully this will help someone else.  Remember, sometimes it takes more than one query to get the job done.  :D

    Robert

    • Marked as answer by greenthnkr Wednesday, September 23, 2015 1:18 PM
    Wednesday, September 23, 2015 1:18 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi. I am not very good with Excel. Can you please explain what your formula is supposed to be doing in plain words? Thanks.
    Monday, September 21, 2015 8:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello fellow Access enthusiasts.  I am trying to re-write an Excel formula in Access and so far have not had good luck.  Here is the formula:

    =COUNTIFS(Tag,"*PC5*",Tag,"<>*APC5*",Tag,"<>*PC5A*",Tag,"<>*NPC5*",Line,"<>*GSOCLAIMS*",Queue,"<>*END*")

    The words TAG, Line and Queue are named ranges of columns, basically columns A,B and G respectively 

    Please help me.  Thank you :)

    Robert


    I can't really say for sure right now but if you check this link you will see that TAG and LINE are both reserved words in Access so may be problematic if not using [] around them, and may be a problem even if bracketed.

    http://allenbrowne.com/AppIssueBadWord.html

    Are you trying to count how many of each of those in columns? so...

    PC5 appears X number of times in column Tag

    NPC5 appears X number of times in column Line

    Etc.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, September 21, 2015 9:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A sticky part comes about because Excel rows don’t necessarily correspond to records in a database, whose records are in arbitrary order. You need to deliberately and intentionally ensure that the Excel row “name” corresponds to the Access record “name”, which means you may have to split up the Access data into more than one table.

    Post a few lines of the Excel data and the results of applying the function…

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, September 22, 2015 1:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to resolve my problem by breaking up the code into two separate queries.  In my first query I entered this code to pick out a majority of the records I didn't need:

    prePC5: IIf([TAG_LINE_CMMT] Like "*PC5*" And [BUS_AREA] Not Like "*GSOCLAIMS*" And [QUEUECD] Not Like "*END*",1,0)

    Then in the second query I put this code to filter out the rest of the records I did not want:

    PC5: IIf([TAG_LINE_CMMT] Like "*PC5A*",0,IIf([TAG_LINE_CMMT] Like "*APC5*",0,[prePC5]))

    I should have mentioned in my original post that 

    Tag = [TAG_LINE_CMMT]

    Line = [BUS_AREA]

    Queue = [QUEUECD]

    Hopefully this will help someone else.  Remember, sometimes it takes more than one query to get the job done.  :D

    Robert

    • Marked as answer by greenthnkr Wednesday, September 23, 2015 1:18 PM
    Wednesday, September 23, 2015 1:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Robert. Congratulations! Glad to hear you found a solution that works for you. Good luck with your project.
    Wednesday, September 23, 2015 5:33 PM