-
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.
-
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
-
Last edited by quint6778; 11-10-2017 at 07:37 PM.
Reason: misread his instruction.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules