parse out a text field

[LGarcia]

Hi all,
I was given a table with a field that contains comma delimited data. The
data in the field looks like this:
100,08/04/2004,3,1,0,,.
I'm using this to parse out the first set of text up to the first comma:
Trim(Left([AuditAfter],InStr([AuditAfter],",")-1))
I'm then using Trim(Mid([AuditAfter],InStr([AuditAfter],",")+1)) to return
the next set of text but it returns everything to the right of the first
comma . Can someone show me how to parse out only the text between the next
set of commas, and then the next...
Thanks!!
LGarcia

 

[fredg]

Hi all,
I was given a table with a field that contains comma delimited data. The
data in the field looks like this:
100,08/04/2004,3,1,0,,.
I'm using this to parse out the first set of text up to the first comma:
Trim(Left([AuditAfter],InStr([AuditAfter],",")-1))
I'm then using Trim(Mid([AuditAfter],InStr([AuditAfter],",")+1)) to return
the next set of text but it returns everything to the right of the first
comma . Can someone show me how to parse out only the text between the next
set of commas, and then the next...
Thanks!!
LGarcia

In a query?

If you have Access 2000 or newer, copy and paste the following code
into a new Module.

Public Function ParseText(TextIn As String, X) As Variant

On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function

=======

Call it from a query:
FirstGrouparseText([FieldName],0)

SecondGrouparseText([FieldName],1)

ThirdGrouparseText([FieldName],2)

etc.
Notice the groups start a Zero.

You'll want to limit records to only those with data in that field.

 

[LGarcia]

Wow! I wasn't aware that you could call a function from a query. It works
beautifully! Thanks!!!

Hi all,
I was given a table with a field that contains comma delimited data. The
data in the field looks like this:
100,08/04/2004,3,1,0,,.
I'm using this to parse out the first set of text up to the first comma:
Trim(Left([AuditAfter],InStr([AuditAfter],",")-1))
I'm then using Trim(Mid([AuditAfter],InStr([AuditAfter],",")+1)) to return
the next set of text but it returns everything to the right of the first
comma . Can someone show me how to parse out only the text between the next
set of commas, and then the next...
Thanks!!
LGarcia

In a query?

If you have Access 2000 or newer, copy and paste the following code
into a new Module.

Public Function ParseText(TextIn As String, X) As Variant

On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function

=======

Call it from a query:
FirstGrouparseText([FieldName],0)

SecondGrouparseText([FieldName],1)

ThirdGrouparseText([FieldName],2)

etc.
Notice the groups start a Zero.

You'll want to limit records to only those with data in that field.

 

[Andre726]

duhhhhhhhhhhhh.............................
Serious brain cramp moment.
After I little thought, found this solution both simple and elegant.
Solves all my paring problems.
Thank you.

This looks like the answer to my problem as well - but a few more details
would help. My table name is Orders. The field I want to parse is called
"description" and the fields I want to fill From description are called
"size", "material" and "grade" respectively.
Question(s):
Is the Function "ParseText" input as given..given the above?
In the query, do I replace firstgroup:...secondgroup:...and thirdgroup:...
with size, material and grade? Use [description] as my Fieldname?
If I get this to work, it solves all my text parsing problems in one fell
swoop.
Thanks.

Wow! I wasn't aware that you could call a function from a query. It works
beautifully! Thanks!!!

On Wed, 4 Aug 2004 11:06:03 -0500, LGarcia wrote:

Hi all,
I was given a table with a field that contains comma delimited data. The
data in the field looks like this:
100,08/04/2004,3,1,0,,.
I'm using this to parse out the first set of text up to the first comma:
Trim(Left([AuditAfter],InStr([AuditAfter],",")-1))
I'm then using Trim(Mid([AuditAfter],InStr([AuditAfter],",")+1)) to return
the next set of text but it returns everything to the right of the first
comma . Can someone show me how to parse out only the text between the next
set of commas, and then the next...
Thanks!!
LGarcia

In a query?

If you have Access 2000 or newer, copy and paste the following code
into a new Module.

Public Function ParseText(TextIn As String, X) As Variant

On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function

=======

Call it from a query:
FirstGrouparseText([FieldName],0)

SecondGrouparseText([FieldName],1)

ThirdGrouparseText([FieldName],2)

etc.
Notice the groups start a Zero.

You'll want to limit records to only those with data in that field.