By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
5 Replies


P: n/a
rkc
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" <rk*@rochester.yabba.dabba.do.rr.bomb> 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.