Results 1 to 10 of 10
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410

    How to match a word from one field to another then exclude it from the other field?

    Mens Bronze 1 Bronze
    Championship Adult Pairs Championship

    1st column is EventTypeDescription (ETD)


    2nd column is EventTypeLevel (ETL)

    Most of the ETD precedes with the ETL, BUT not always, such as the "Mens BRONZE 1"

    What I'm trying to do is remove the ETL that exists in the ETD field and show the ETD without the redundant ETL information.

    What is the best way to do this?

    Either in SQL (though the db is SQL) or in Access query or Access module / VBA?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    What about in the query..instead of showing [ETL]..put a virutal field with an IIF:
    Fld2: IIF(INSTR([ETD],[ETL]=0,[ETL],"")

    If etl is in the 1st fld etd then dont show it, else show it.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Not sure how InStr works but I was able to change it to this:

    Code:
    Event: IIf(InStr([EventType],[EventLevel]=0),[EventLevel],"")
    and got
    Event
    #Error


    And this
    Code:
     Event: IIf(InStr([EventType],[EventLevel])=0,[EventLevel],"")
    shows blank for all reccords

  4. #4
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Guess this can't be done?

  5. #5
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Try it this way:
    Code:
    UPDATE YourTable SET EventTypeDescription=Trim(Left(EventTypeDescription;Len(EventTypeDescription)-Len(EventTypeLevel)) WHERE Right(EventTypeDescription,Len(EventTypeLevel))=EventTypeLevel

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Hello hapm,
    there isn't anything to Update in the table.
    or rather i can't update the field in the table.

    as mentioned it's from a SQL table that cannot be changed.
    so the query or code will have to handle this.

  7. #7
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Using the where part of the update as the condition in the Iif, the value expression as the second parameter and the EventTypeDescription as the third will work as a calculated field as well.

  8. #8
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Use your example, called the table "EventType" and 1st column is EventTypeDescription and 2nd column is EventTypeLevel.

    SELECT IIf(REPLACE(EventType.EventTypeDescription, EventType.EventTypeLevel, "") AS EVENT, EventType.EventTypeDescription, EventType.EventTypeLevel
    FROM EventType;

    NOTE: The only drawback is the space before and/or after the word that you are looking for. There is a way to do it use INSTR and MID.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    to expand on LFPM062010's response (which is the way I'd do it as well) you can you could do a nested iif as well to account for a space either before, or after the EventTypeLevel description in the EventTypeDescription field.

    iif(Instr([EventTypeDescription], " " & EventTypeLevel) >0, replace([EventTypeDescription], " " & [EventTypeLevel], "", iif(Instr([EventTypeDescription], EventTypeLevel & " ") >0, replace([EventTypeDescription], [EventTypeLevel & " "], "",[EventTypeDescription])) AS EventModified

    the first instr is looking for the string " <ETL>" and replacing it if exists
    the second instr is looking for the string "<ETL> " and replacing it if it exists
    otherwise it just displays the ETL unadultered.

    I didn't test this but the concept is sound.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    just thought of something, you could do nested replaces too

    iif(instr([EventTypeDescription], [EventTypeLevel]) = 0, [EventTypeLevel], (replace(replace([EventTypeDescription], [EventTypeLevel], ""), " ", " ")))

    EDIT
    not sure if it will allow searching for double spaces you may have to use the ascii character

    chr(32)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-23-2013, 01:42 PM
  2. Replies: 8
    Last Post: 04-14-2013, 01:33 PM
  3. Replies: 4
    Last Post: 01-30-2012, 08:32 AM
  4. exclude field in query
    By chrisy in forum Queries
    Replies: 2
    Last Post: 10-28-2011, 09:53 AM
  5. Exclude rows to get to data/field names
    By The Stig in forum Access
    Replies: 1
    Last Post: 06-27-2010, 09:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums