431,852 Members | 2,152 Online
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

# Parsing Problem

 P: n/a I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? Thanks, RBollinger Nov 13 '05 #1
5 Replies

 P: n/a robboll wrote: I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? Assuming AccessVersion > Access97 s = "abc-de-fgh-xyz" i = InStrRev(s, "-") If i > 0 Then Debug.Print Mid\$(s, i + 1) End If Nov 13 '05 #2

 P: n/a robboll wrote: I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? If you have A2K, the function RInstr() is built in. You can use that to find the last occurrence of the dash. I don't have RInstr so see help for the exact syntax and result interpretation. I'd expect something like Mid(fld1,rinstr(fld1,"-")+1) will yield the fld2 value. -- Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html Nov 13 '05 #3

 P: n/a You could use an Update Query First add fld1 and fld2 to your table, if you haven't already. Then use a query like this: UPDATE MyTable SET fld1=left(Field1,instr(Field1,"-")-1), fld2=mid(Field1,instr(Field1,"-")) This should work on any record where there's a hyphen. If you have records with no hyphens, that's another matter. HTH "rkc" wrote in message news:PG*****************@twister.nyroc.rr.com... robboll wrote: I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? Assuming AccessVersion > Access97 s = "abc-de-fgh-xyz" i = InStrRev(s, "-") If i > 0 Then Debug.Print Mid\$(s, i + 1) End If Nov 13 '05 #4

 P: n/a On 20 Jun 2005 04:07:32 -0700, robboll wrote: I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? Thanks, RBollinger If you're version of Access supports the InStrRev() function, you can use: Fld1 = Left([Combined],InStrRev([Combined],"-")-1) Fld2 = Mid([Combined],InStrRev([Combined],"-")+1) If you're version of Access does not support InStrRev, you need to create a User Defined function to parse the data. Public Function Parse(StrIn As String, Pos As String) As String Dim intX As Integer Dim intY As Integer intX = InStr(StrIn, "-") If intX = 0 Then Parse = StrIn Exit Function Else Do While intX <> 0 intY = intX + 1 intX = InStr(intY, StrIn, "-") Loop If Pos = "R" Then ' Value to the right of the last hyphen Parse = Mid(StrIn, intY) Else ' Value to the left of th last hyphen Parse = Left(StrIn, intY - 2) End If End If End Function You can call it from a query: Fld1:Parse([Combined],"L") Fld2:Parse([Combined],"R") or an Unbound Text Control: =Parse([Combined],"L") =Parse([Combined],"R") Post back if you still need help. -- Fred Please only reply to this newsgroup. I do not reply to personal email. Nov 13 '05 #5

 P: n/a Wow -- 80 ways to do this. I'll start with yours. Thanks! rkc wrote: robboll wrote: I have a field with entries like: Field1 abc-de-fgh-xyz abc-ab abcdefg-123 and I need to put the data into two seperate fields so that everything after the last hyphen "-" is put in a seperate field. Example Fld1 Fld2 abc-de-fgh xyz abc ab abcdefg 123 Any function ideas whether built in or created? Assuming AccessVersion > Access97 s = "abc-de-fgh-xyz" i = InStrRev(s, "-") If i > 0 Then Debug.Print Mid\$(s, i + 1) End If Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.