Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing Data (2000)

    I didn't design this database and the one who did knew even less than I do. At any rate, one of the fields in the tblEmployee, is of course "EmployeeName".

    The data is being input this way:

    Lastname, Firstname, Date (2 spaces between Firstname, and Date).

    I know how to Parse the Firstname but can't figure out how to Parse out the rest....can anyone please help me?

    I would really appreciate it.

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Parsing Data (2000)

    Parsing strings dependably usually hinges on what you know to be ALWAYS true about the string. For example, is the date in a consistent format (such as xx/xx/xx)? Or (as you stated), the a comma and 2 spaces separates the first name from the date.

    If you knew the format of the date, you could easily use the Right function to get the information, something like:
    = Right(inputstring,8)

    If the date format is not consistent, but you know that the date follows the the comma and 2 spaces (and there is no other occurance of this), then you could use this:
    = Mid(inputstring, instr(inputstring,", ") + 3)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parsing Data (2000)

    One alternative would be to loop through the recordset using the Split function to assign the contents of the field to a single dimension array and using the comma as the delimiter. You should come out with an array that has one element for each of the words in the string. Then you can use code to extract each element of the array and write it into an individual field. Your code will need also to trim the value of the dimension and to convert the date to an actual date if you're storing it in a date/time field.

    Of course, if you don't have reliable data--that is, if there are records with no comma between any two of the elements or with missing data--some of the results will be bad and will have to be hand manipulated.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    Mark,

    I tried the latter of the 2 suggestions because the data might be Newton, Roberta, 7/18/01 or Newton, Roberta, 07/18/01 (note the # of chars).

    At any rate, I did have to make a few changes...

    i.e. Mid([NameOfField],Instr[NameOfField],",")+11)..once I did that it worked...thanks for your help.

    However, I am still lost at how to get the FIRSTNAME out the string? Do you have a suggestion for that?

    Again, Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    Charlotte,

    I am interested in using this method as there are several tables I will need to apply this to. However, I don't know where to begin to write the code to do this...could you please provide me with more help...I would like to make this a public function, if possible.

    Thanks.

    p.s. The data is all the same in the respect that it has LastName, FirstName, Date...this is all as a string...however, the date may be 07/18/01 or 7/18/01..so the # of chars may differ.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing Data (2000)

    Assuming this works the same as it does in Word (big assumption?), you could use Split this way:

    Public Type myData
    NameLast As String
    NameFirst As String
    myDate As Date
    End Type

    Sub test1()
    Dim myBlob As myData
    myBlob = ParseNameDate("Smith, Joe, 1/1/95") 'type your test data here
    If myBlob.NameLast = "DATAERROR" Then
    MsgBox "Could not distinguish all 3 data elements."
    Else
    MsgBox "First Name = " & myBlob.NameFirst & vbCrLf & _
    "Last Name = " & myBlob.NameLast & vbCrLf & _
    "Date = " & myBlob.myDate
    End If
    End Sub

    Public Function ParseNameDate(strNameDate As String) As myData
    Dim strArray() As String
    strArray = Split(strNameDate, ",", , vbTextCompare)
    If UBound(strArray) = 2 Then 'you have all 3 strings
    ParseNameDate.NameLast = Trim(strArray(0))
    ParseNameDate.NameFirst = Trim(strArray(1))
    ParseNameDate.myDate = CDate(strArray(2))
    Else
    ParseNameDate.NameLast = "DATAERROR"
    End If
    End Function

    I apologize for using my own "Type" of variable, but it was the quickest way I could think of to return all the data from the Function in one blob. I like using the CDate function to scrub dates into a proper format. I should add error checking, but... might I leave that to you or others? Note: it is important to specify a Text compare where this is an option in Access, otherwise you run into errors because the default tends to be DatabaseCompare.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    J,

    I just tested your code and I think it is what I want..I think I did something wrong tho..

    I put all the code in a module...

    I changed the line of code in test1() to represent a name that was in my table.....i.e. Newton, Roberta, 7/18/00

    I created a form with a control button on it and set the OnClick event to "=test1()"

    However, when I click on it nothing happens...that tells me I have done something wrong....What?

    Also, from reading the code, I can't tell where the fields are being updated in the tblTimeEntry...should I add that code...is that the problem?

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing Data (2000)

    Sorry, my Access experience mostly involves queries; I have never created a form in Access, so I don't know how to connect a button to a code module. All I did in this case was test the concept by putting my insertion point in the test1() procedure and press the F5 key.

    As you surmised, I did not write code to update the table. I assume you would do that by assigning the 3 elements of the myData structure to the 3 new fields you are moving the data into. But again, I really have no idea. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parsing Data (2000)

    It works the same way in Access. The Split function is part of VBA. And there's nothing wrong with using the custom type to pass the data back, but it depends on where and how the result is going to be used. It won't work in a query, for example.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parsing Data (2000)

    All the routine does is pass a user-defined type back to the calling routine. The user-defined type simply contains the parts of the field you passed into the routine. It is only one way to pass the data back. It could also have been passed back as an array instead, or through arguments passed ByRef to the routine and populated there for use by the calling routine.

    *You* have to write the code to take that result and write it to the table. A form is a highly unlike place to do this from, so you need to give more information about what you're actually trying to do. You haven't given us any information on your table structure or even what datatype the field you want the date put into is. We're all willing to help, but we need the basic information to make the effort productive.
    Charlotte

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing Data (2000)

    > It could also have been passed back as an array instead

    I guess I assumed you would want to convert the date string into a date type. If that is not the case, an array would be simpler.

    Another option would be to use module-scope variables that the function could change, and then it wouldn't have to return anything but a success/failure indication.

    So many options. So little time. <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    Charlotte,

    Thanks for you help...I tested J's and he is correct, when I hit F5 it returns the values in a dialog box. but the Parsing worked PERFECTLY.

    My table structure is as follows:

    tablename=tblEmployee
    with the following fields

    strFullName=LastName, FirstName, Date---as TEXT
    strLastName=Needs to be last name of strFullName---TEXT
    strFirstName=Needs to be last name of strFullName---TEXT
    WED=Needs to be the date that is in strFullName----DATE.

    I thought a query would work something along the line of Parse or Trim...but I don't know the correct syntax for it. J's solution worked in VB...how can I implement that?

    I know how to write the code to update fields, I'm just not sure how to implement the update code with the rest of J's code and how to make it work without hitting the F5 key...that's why I chose the button on a form. If you think that is the wrong approach, could you please offer me another one.

    My problem is I'm not sure where to go from here....

    I appreciate everyone's help with this...

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Parsing Data (2000)

    Getting the first name depends largely on how consistent data entry was. If you KNOW that the LastName and FirstName are separated by a comma a single space, and you KNOW there is always a comma and 2 spaces following the last name, you could use the following code (you could actually make this into 1 large command, but I broke it up because it is easier to follow):

    dim FirstNameStart as integer
    dim FirstNameEnd as integer
    dim FirstNameLength as integer

    FirstNameStart = instr(string,", ") + 2
    FirstNameEnd = instr(string,", ") - 1
    FirstNameLength=FirstNameEnd - FirstNameStart + 1
    FirstName = mid(string, FirstNameStart, FirstNameLength)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    In my non-Lounge life (actually in the lounge too come to think of it), I'm pretty well known for my shortcuts. I can get to the pub 5 minutes before my co-workers, anyway, if this is time critical and a one time thing, you might consider exporting to Excel and using its Text to Columns tool under Data. Then bring the results back into Access. The process is quite intuitive and has the added benefit of being easy to visually scan for anomalies. I'm using Off '97 but I'm fairly confident it's available in 2000. Some folks might call it cheating, I just think if one part of the suite does something easier/better than another, why not use it? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  15. #15
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing Data (2000)

    Thank you very much.
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    I was searching the forums looking for information on parsing last name and first name from a string. My string was in one cell in Excel and I needed to get it broken out into two cells for later input to Access. I had been copying the entire range to Word, then doing a column-to-text then a text-to-column switch and then pasting back into Excel.

    This quick tip, has saved me alot of time, several times during the month. Again Thanks and keep up the good posting.

Page 1 of 2 12 LastLast

Posting Permissions

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