Parsing in a query

[deb]

I am using Access97.


The field in the query looks like...

John|A|Doe|30Mar2003|G1

I need to break this down into seperate fields...

Name Date Dept
John A Doe 30Mar2003 G1

What is the best way to parse this type of data?

Thanks in advance

 

[Corticus]

Hi deb,

I created a little function that should help you out.

Go into tools|macro|visual basic editor and insert a module.

Paste this code into the module:

Function Delim(str As String, ind As Integer)

    Dim arr1 As Variant
    Dim ct As Integer
    Dim j As Integer, i As Integer
    Dim x

    j = 0
    ct = 1

    For i = 1 To Len(str)
        If Right(Left(str, i), 1) = "|" Then
            ct = ct + 1
        End If
    Next i
    
    ReDim arr1(1 To ct)
    
    For i = 1 To ct
        Do
            If j > Len(str) Then Exit Do
            If Left(Right(str, Len(str) - j), 1) = "|" Then Exit Do
            x = Left(Right(str, Len(str) - j), 1)
            arr1(i) = arr1(i) & x
            j = j + 1
        Loop
        j = j + 1
    Next i

    Delim = arr1(ind)
    
End Function

This is a new function that you can use in a query to pull an indicated field from your string(John|A|Doe|30Mar2003|G1). The function is delim(str, ind) and has two arguments, str being the string with the pipes("|"), and ind being the field number (G1 is 5). You can use this as an expression in a query.

Start a new field in the query that has this data, and enter this where it says field in the design grid for the query:
Name: delim([string_field],1) & " " & delim([string_field],2) & " " & delim([string_field],3)

Now start a new field and enter this:
Date: delim([string_field],4)

And finally, the dept field:
Dept: delim([string_field],5)

I xan send you an example, but it will be in Access2000, but the method should work in 97.

HTH,

 

[deb]

HTH,

Thank you for your help.

You offered an example of the delem function, this would be very appreciated. I also have Access2K, so I can try this out.

Thanks again,

 

[Corticus]

No problem deb,

Here's the file:
http://www.theillumni.com/posts/delimit.zip

Hope this helps,

 

[deb]

The Delim function works wonderfully, but how do I now get the date field to be a date data type?

I am unable to use the criteria to search the field like a date field...


Between #5/1/02# and #5/30/03#

 

[Corticus]

http://www.mrexcel.com/board2/viewtopic.php?t=61661

I re-uploaded the file to include a solution to this question.

Glad the function worked, user defined functions are tricky to explain how to use, but once in place, can simplify things a great deal.

-Cort

 

[deb]

Thank you for everything.

This works great!!

 

[Corticus]

A little add-on,

I changed the formula a bit to allow an argument to specify the delimiting character like:
Parse(string,character,index)

String being the delimited string of test, character being the delimiting character, and index being an integer representing the element from the delimited list you need (1st field = 1, 2nd 2 etx)

Like:
Name: parse([string_field],"|",1) & " " & parse([string_field],"|",2) & " " & parse([string_field],"|",3)
for the name.

Here's the new formula:

Function parse(str As String, chr As String, ind As Integer)

    Dim arr1 As Variant
    Dim ct As Integer
    Dim j As Integer, i As Integer
    Dim x

    j = 0
    ct = 1

    For i = 1 To Len(str)
        If Right(Left(str, i), 1) = "|" Then
            ct = ct + 1
        End If
    Next i
    
    ReDim arr1(1 To ct)
    
    For i = 1 To ct
        Do
            If j > Len(str) Then Exit Do
            If Left(Right(str, Len(str) - j), 1) = chr Then Exit Do
            x = Left(Right(str, Len(str) - j), 1)
            arr1(i) = arr1(i) & x
            j = j + 1
        Loop
        j = j + Len(chr)
    Next i

    parse = arr1(ind)
    
End Function

Hope this is useful to somebody,

 

[Corticus]

HI again all,

Found out the vb function split() takes care of a bunch of this code, faster too.

Check it out:

Function parse(str As String, chr As String, ind As Integer)

    Dim arr_str As Variant

    arr_str = Split(str, chr)
    parse = arr_str(ind - 1)

End Function

HTH,

 

[bat17]

But just bear in mind that the Split Function was not introduced until Access 2000

Peter