Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: parsing a delimited string

    Hello, I am having a hard time parsing a delimited string. I am a novice at VBA and have been struggling with the sources I find out there that explain how to do this.

    I have a temp table [ExcelLegalFactorsCapEarly] that is imported from Excel that contains a records with a number of fields. Among them are the following (with one sample record

    [ClaimNumber] = 12345 [InjuryType] = 1;3;4;6;7;9

    The [ClaimNumber] is the primary key on this table.

    There can be up to 25 different InjuryTypes. Each InjuryType number corresponds to a description that I have in another table called [tblInjuryTypeList].

    I need to parse the Injury Types field and append them to another table called [tblInjuriesperClaimECA] so that the fields look like this:

    [ClaimNumber] = 12345 [InjuryType] = 1
    [ClaimNumber] = 12345 [InjuryType] = 3
    [ClaimNumber] = 12345 [InjuryType] = 4
    [ClaimNumber] = 12345 [InjuryType] = 6
    [ClaimNumber] = 12345 [InjuryType] = 7
    [ClaimNumber] = 12345 [InjuryType] = 9

    The combination of the [ClaimNumber] and the [InjuryType] make up the primary key on this table.

    What is the syntax to do this? So far, I have created two queries, one that appends the [ClaimNumber] and first [InjuryType]. The syntax on this query is as follows:

    INSERT INTO tblInjuriesperClaimECA ( ClaimNumber, InjuryType )
    SELECT ExcelLegalFactorsCapEarly.[ClaimNumber], Left([InjuryType],InStr(1,[InjuryType],";")-1) AS Expr1
    FROM ExcelLegalFactorsCapEarly;

    The second query removes the first InjuryType:
    UPDATE ExcelLegalFactorsCapEarly SET ExcelLegalFactorsCapEarly.[InjuryType] = Mid([InjuryType],InStr(1,[InjuryType],";")+1);

    I have luck with the queries for the first Injury Type but am stuck from there. I don't know how to Loop till it is done adding all the Injury types.

    Sorry this is so long, but I wanted to be thorough!

    Thanks in advance for your help.

    Grace

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: parsing a delimited string

    Originally posted by palto1
    Hello, I am having a hard time parsing a delimited string. I am a novice at VBA and have been struggling with the sources I find out there that explain how to do this.

    I have a temp table [ExcelLegalFactorsCapEarly] that is imported from Excel that contains a records with a number of fields. Among them are the following (with one sample record

    [ClaimNumber] = 12345 [InjuryType] = 1;3;4;6;7;9

    The [ClaimNumber] is the primary key on this table.

    There can be up to 25 different InjuryTypes. Each InjuryType number corresponds to a description that I have in another table called [tblInjuryTypeList].

    I need to parse the Injury Types field and append them to another table called [tblInjuriesperClaimECA] so that the fields look like this:

    [ClaimNumber] = 12345 [InjuryType] = 1
    [ClaimNumber] = 12345 [InjuryType] = 3
    [ClaimNumber] = 12345 [InjuryType] = 4
    [ClaimNumber] = 12345 [InjuryType] = 6
    [ClaimNumber] = 12345 [InjuryType] = 7
    [ClaimNumber] = 12345 [InjuryType] = 9

    The combination of the [ClaimNumber] and the [InjuryType] make up the primary key on this table.

    What is the syntax to do this? So far, I have created two queries, one that appends the [ClaimNumber] and first [InjuryType]. The syntax on this query is as follows:

    INSERT INTO tblInjuriesperClaimECA ( ClaimNumber, InjuryType )
    SELECT ExcelLegalFactorsCapEarly.[ClaimNumber], Left([InjuryType],InStr(1,[InjuryType],";")-1) AS Expr1
    FROM ExcelLegalFactorsCapEarly;

    The second query removes the first InjuryType:
    UPDATE ExcelLegalFactorsCapEarly SET ExcelLegalFactorsCapEarly.[InjuryType] = Mid([InjuryType],InStr(1,[InjuryType],";")+1);

    I have luck with the queries for the first Injury Type but am stuck from there. I don't know how to Loop till it is done adding all the Injury types.

    Sorry this is so long, but I wanted to be thorough!

    Thanks in advance for your help.

    Grace
    Dim ParseString As String

    ParseString=Split(SourceString,";",vbTextCompare)

    MsgBox ParseString(0), "1st element of the array"

  3. #3
    Join Date
    Mar 2004
    Posts
    68

    Red face Still don't understand Parse problem

    Dim ParseString As String

    ParseString=Split(SourceString,";",vbTextCompare)

    MsgBox ParseString(0), "1st element of the array"

    I'm sorry, but I don't understand what you are saying to do. Is "Split" a function? As I mentioned earlier, I am a novice in this area. Could you help by adding some comments in the above code?

    Thank you,
    Grace

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Split is a function available in MSAccess 200x

    ParseString is a String Array
    SourceString is to hold what is in [InjuryType]

    Do as M Owen has suggested then to read the array (as a sample) use the following code:
    Code:
    Dim i as integer, a$
    For i = 0 to UBound(ParseString)
      a$ = a$ & ParseString(i) & vbNewLine
    Next i
    MsgBox"Your Array Contains:" & vbNewLine & vbNewLine & a$

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You should really learn how to use the help file, you're making it way to hard on yourself:

    Split Function


    Description

    Returns a zero-based, one-dimensional array containing a specified number of substrings.

    Syntax

    Split(expression[, delimiter[, limit[, compare]]])

    The Split function syntax has these named arguments:

    Part Description
    expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.

    delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.

    limit Optional. Number of substrings to be returned; 1 indicates that all substrings are returned.

    compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values



    That is in the vba help file under "split function"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •