Parsing Data (2000)

Topic

New Reply

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,

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

> 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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Thank you very much.

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.

Reply | Quote

Wow, Steve 9 smilies, you make me .
Glad it helped.

Reply | Quote