Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19

    Split a Field into Multiple Fields?

    Hello,

    I have the following field that I would like to split into individual fields within a query:

    [YarnCode]: 1042/23S-0/2195-BR027

    What I want:


    • 1042
    • 23S-0
    • 2195
    • BR027


    I have the following code that I use on something similar, but I cannot get it to work due to the different separators ( "-" and "/"):


    • Code: SplitFile(0,[YarnCode],"/")


    • Twist: SplitFile(1,[YarnCode],"/")
    • Quality: SplitFile(2,[YarnCode],"/")
    • Colour: SplitFile(3,[YarnCode],"-")


    I am not sure if this is the correct approach for this?

    Any advice as to how to approach this please?


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You will have to nest SplitFile for last 2 elements.

    Quality: SplitFile(0, SplitFile(2,[YarnCode],"/"), "-")

    Colour: SplitFile(1, SplitFile(2,[YarnCode],"/"), "-")
    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.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Excuse my ignorance but is SplitFile a user defined function you have created?
    I know how to use the built-in Access Split function but have never heard of SpliFile
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  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,822
    I assumed it is UDF because Split() won't work in query. If my suggestion doesn't work, will need to know more about the underlying 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.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That was part of the reason I asked.
    If the OP is trying to use Split in a query it will fail
    However, Daniel Pineault has a StringSplit function as a workaround to allow Split to be used in a query https://www.devhut.net/2018/04/22/ac...it-in-a-query/
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19
    Thank you all, I managed to get it working using the SplitFile

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

Similar Threads

  1. Sort Multiple Fields in a Split form
    By NateL in forum Access
    Replies: 3
    Last Post: 05-01-2014, 01:38 PM
  2. Replies: 2
    Last Post: 08-10-2012, 03:42 PM
  3. Replies: 3
    Last Post: 02-24-2012, 06:20 PM
  4. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  5. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM

Tags for this Thread

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