Results 1 to 4 of 4

SplitIndex(,)()

  1. #1
    quint6778 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    2

    SplitIndex(,)()

    Hello all, not sure in im in the right place or not but i think someone can assist here....



    I would like to know what each letter (A,B,C, etc) in the bottom code is doing to the number above it. Each line is its own column on a "Make Table" query

    [ID] = 70007|75|524|AA

    A: "0" & Left([ID],6)

    B: splitIndex([ID],"|",1)

    C: splitIndex([ID],"|",2)

    ADJ_C: IIf(Len([c])=1,"000" & [c],IIf(Len([c])=2,"00" & [c],IIf(Len([c])=3,"0" & [c],"?")))

    D: IIf(Len(Trim(splitIndex([ID],"|",3)))=2, Trim(splitIndex([ID],"|",3)),"-" & Trim(splitIndex([ID],"|",3)))

    ID_ADJ: [a] & [b] & [adj_C] & [d]

    Thank you in advance!
    I am unfamiliar with this database and im trying to troubleshoot a report that runs thru it.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,022
    if you ran it, you would see the results. If you want to run it and not make a table, in query design view change from make table to select query. The query is reformatting 70007|75|524|AA to 070007750524AA

  3. #3
    quint6778 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    2
    good idea, thank you!!!
    Last edited by quint6778; 11-10-2017 at 06:37 PM. Reason: misread his instruction.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,022
    A: "0" & Left([ID],6) -add a 0 to the first six characters

    B: splitIndex([ID],"|",1) - splitindex is taking the second element of the string with the | as a separator (first element would be 0)

    C: splitIndex([ID],"|",2) - see above

    ADJ_C: IIf(Len([c])=1,"000" & [c],IIf(Len([c])=2,"00" & [c],IIf(Len([c])=3,"0" & [c],"?"))) - makes the third element a standard length of 4 chars by padding with zeros (could have used the format command above - C:format(splitIndex([ID],"|",2),"0000")- and not used this term

    D: IIf(Len(Trim(splitIndex([ID],"|",3)))=2, Trim(splitIndex([ID],"|",3)),"-" & Trim(splitIndex([ID],"|",3))) - adds a - to the fourth element if the length does not equal 2

    ID_ADJ: [a] & [b] & [adj_C] & [d] - concats the modified values together

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

Posting Permissions

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