Results 1 to 14 of 14
  1. #1
    AndyDandy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Posts
    7

    How to extract concatenated fields

    I pull various adhoc reports from tables in a system and use MS-Access to do so. I cannot change the system tables in any way but I can create new ones. One of these system tables is called CATEGORIES that contains lookup information. There are probably 28 entries in this table. Pretty straightforward stuff.



    CATEGORIES
    CATEG DESCRIP ABBREV
    I Recertification RECERT
    A Initial Certification INCERT
    3 License Renew LICREN

    The rub comes as other tables in this system have concatenated values that use these survey codes: A, IA, AI3 etc... as values. I can easily extract these in a query using LIKE, but I would like to be able to work more with these concatenated codes to produce a report that would display "RECERT, INCERT" for a concatenated value of "IA".

    If anyone has ideas directing me to ways of working with table data in this concatenated format it would be appreciated. I cannot even think how I would even search effectively for this topic. Maybe the answer is not possible.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Code:
    Public Function ExtractCode(arg As String) As String
        Dim x As Long
        Dim target As String
        Dim rslt As String
        For x = 1 To Len(arg)
            target = InStr(arg, x)
            'Debug.Print target
            rslt = DLookup("Abbrev", "Categories", "Categ='" & target & "'")
            Debug.Print rslt
        Next x
    End Function

    Here's a way to extract it. You'll have to adapt it for your situation.

    You use it by calling the function with your concatenated string:
    Code:
    Call ExtractCode(my_concat_string)

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    In your query; join the Look Up table to the data table on the common field. Then those fields are available to use.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The data source is storing multiple category values as a single concatenated string instead of a record for each selected category? That sucks! So you would need a lookup table that has a record for every possible combination of category codes. How many codes can be combined? 3, 4, all 28? In any order? A code solution is sounding better by the minute but even then the results might not be consistent. One record could have IA and another AI so the equivalents would be "RECERT, INCERT" and "INCERT, RECERT". Is this acceptable? I would not like it.

    Is there validation on the source data entry to prevent duplication of categories? Would AI3A be possible?

    Dealing with GIGO syndrome - Garbage In Garbage Out - is never straightforward and hardly ever satisfying.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm leaning towards the code method as well unless there's a calculation in a query field, but one isn't jumping out at me as it would probably require mixing of Left and IN functions. I say that because I interpreted the original post to mean a lookup table doesn't exist - the lookup values come from the same table. Maybe I'm wrong on that.

    At any rate, I don't see that code working because the start position isn't changing for the Instr function, so it will always start at 1, plus the string to search for isn't actually being provided - just the string to search (arg) but the parameter for the string to find is being passed an integer (x) which is really the counter and not a string value. Also, the line that sets the value of rslt isn't being concatenated, so it will just be over-written on every loop. It is a very good start, though, and I apologize if you tested it and it worked. I could be misinterpreting something.

    I also think that what was asked for is a comma separated concatenation ("RECERT, INCERT"). So what I think would work is
    Code:
    Public Function ExtractCode(arg As String) As String
    Dim x As Long
    Dim rslt As String
    For x = 1 To Len(arg)
      rslt = rslt & DLookup("Abbrev", "Categories", "Categ='" & Mid(arg, x, 1) & "'") & ","
    Next x
      rslt = Left(rslt, Len(rslt) - 1) 'Trim the trailing ","
      'Debug.Print rslt
    End Function
    if it is called like
    extractcode("IA")

    I haven't allowed for the DLookup ever returning a null, such as if a character in the supplied string isn't found.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    AndyDandy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Posts
    7
    These values would be in a specific sequence; AI3 would always be in that sequence but yes, a lookup table with those combinations was staring at me. This field value is derived by users clicking check boxes so in theory, I would need to include all 28 even though some combinations are not correct, user error could produce them.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So have you attempted the code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how about adapting the concatrelated function?

    http://allenbrowne.com/func-concat.html

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How does ConcatRelated pertain to this situation? Data is already a concatenated CSV string. The requirement is to parse the string and substitute values for each record, not combine records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you pass through AIF' whatever and adapt contactrelated to parse through the string to look up the relevent values from the categories table

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Isn't that what micron's code accomplishes? I think adapting Allen's code would just be more complicated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    possibly, was just suggesting it as an alternative with all the protections

  13. #13
    AndyDandy is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Posts
    7
    Sorry so late on the reply June7. I have tried it but received a null value error.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    And why is that? Are there records with no value in the field? Or is a character in the string not in the lookup table? Micron stated the code is not designed to handle null return.

    Have you done any debug effort?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Search Criteria for Concatenated Fields?
    By schulzy175 in forum Programming
    Replies: 10
    Last Post: 10-24-2017, 12:26 PM
  2. Replies: 9
    Last Post: 06-16-2017, 06:00 PM
  3. Replies: 2
    Last Post: 07-02-2015, 02:24 PM
  4. combobox won't display concatenated fields
    By merlin777 in forum Forms
    Replies: 4
    Last Post: 10-25-2014, 04:36 PM
  5. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 AM

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