Parsing a Text String into 4 Different Fields

R

RayToddJr

I have a form that contains a combo box (cboDefendantsName) that contains the
following field:

DefendantName: [LastName] & (", "+[FirstName] & " " & [MiddleName] & " " &
[Suffix])

If the user enters a name that isn't in the list, it will open dataentry
form to allow the user to put this information in the table.

When the data entry form is opened, I would like the information to be
transferred from the originating form via openargs. Once the data is in the
data entry form, I would like for it to be broken down into the four fields
so as to keep the user from having to enter the information twice (one in the
original form and second n the data entry form).

The data would need to be parsed into

LastName
FirstName
MiddleName
Suffix

Keep in mind that the lastname field is also used as a company name and
therefore may not have a "," after it.

Can anyone please share with me on how to parse this field?

Thanks,

Ray.
 
D

Dale Fye

Well, you might use the Split command to split the data up in the data entry
forms load event. The problem is deciding which field to put it in,
especially WRT you last comment about this possibly being a "Company name"
rather than a person.

I guess you could first check to see if there is a comma. If so, take the
whole text and put it in some field (lastname does not seem to fit here).
Then, if there is a comma, you could use Left$() to get everything to the
left of the comma, and put it in the last name. Then use Mid$() to chop off
the last name so that you have a subset of the data to work with. Then you
could use the split to parse the remainder based on spaces, or you could use
the InStr( ) function and look for spaces as your break points for FirstName
and Middle Name.
 
R

RayToddJr

After reading Dale's input, I created the following code to breakdown the
combined name into individual fields.

I am welcoming any feedback that anyone may see with how I have it written.
Everyone is welcome to use and modify as needed for their own use.

Thanks,

Ray.



Dim PassedText As String
Dim ParsedText As String

Dim MyPosition As Long
Dim mylength As Long


'Verify that the Openargs Passed Data
If IsNull(PassedText) Then
Exit Sub
Else

PassedText = Me.OpenArgs
Me.txtpassedtext = PassedText
MyPosition = 0

'Test to see if there is a comma in the passed text. If there isn't one
this is
'a company and should go into the LastName field.

MyPosition = InStr(1, PassedText, ",")
If MyPosition = 0 Then
Me.LastName = PassedText
Else
'Seeking the Last Name.
MyPosition = 0
MyPosition = InStr(1, PassedText, ",")
MyPosition = MyPosition - 1
ParsedText = Trim(Left(PassedText, MyPosition))
Me.LastName = ParsedText
ParsedText = ""

'Moves the starting position to the first letter of the First
Name, ignoring the comma.
MyPosition = MyPosition + 2

'Seeking the First Name.
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
MyPosition = InStr(1, PassedText, Chr$(32))
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.FirstName = ParsedText
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Middle Name.

MyPosition = InStr(1, PassedText, Chr$(32))

If MyPosition = 0 Then
ParsedText = Trim(Mid(PassedText, 1))
Me.MiddleName = ParsedText
Else
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.MiddleName = ParsedText
MyPosition = MyPosition + 1
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Suffix
If Not IsNull(Trim(PassedText)) Then Me.Suffix =
Trim(PassedText)
End If
End If
End If










RayToddJr said:
I have a form that contains a combo box (cboDefendantsName) that contains the
following field:

DefendantName: [LastName] & (", "+[FirstName] & " " & [MiddleName] & " " &
[Suffix])

If the user enters a name that isn't in the list, it will open dataentry
form to allow the user to put this information in the table.

When the data entry form is opened, I would like the information to be
transferred from the originating form via openargs. Once the data is in the
data entry form, I would like for it to be broken down into the four fields
so as to keep the user from having to enter the information twice (one in the
original form and second n the data entry form).

The data would need to be parsed into

LastName
FirstName
MiddleName
Suffix

Keep in mind that the lastname field is also used as a company name and
therefore may not have a "," after it.

Can anyone please share with me on how to parse this field?

Thanks,

Ray.
Click to expand...
 
R

RayToddJr

Sorry, but posted the wrong version of the code. This one correctly breakes
down the combined name no matter if a user enters any of the following
examples:

Doe, John
Doe, John R
Doe, John Robert
Doe, John Robert Jr

Dim PassedText As String
Dim ParsedText As String

Dim MyPosition As Long
Dim mylength As Long


'Verify that the Openargs Passed Data
If IsNull(PassedText) Then
Exit Sub
Else

PassedText = Me.OpenArgs
Me.txtpassedtext = PassedText
MyPosition = 0

'Test to see if there is a comma in the passed text. If there isn't one
this is
'a company and should go into the LastName field.

MyPosition = InStr(1, PassedText, ",")
If MyPosition = 0 Then
Me.LastName = PassedText
Else
'Seeking the Last Name.
MyPosition = 0
MyPosition = InStr(1, PassedText, ",")
MyPosition = MyPosition - 1
ParsedText = Trim(Left(PassedText, MyPosition))
Me.LastName = ParsedText
ParsedText = ""

'Moves the starting position to the first letter of the First
Name, ignoring the comma.
MyPosition = MyPosition + 2

'Seeking the First Name.
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
MyPosition = InStr(1, PassedText, Chr$(32))

If MyPosition = 0 Then
ParsedText = Trim(Mid(PassedText, 1))
Me.FirstName = ParsedText
Else
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.FirstName = ParsedText
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Middle Name.
MyPosition = InStr(1, PassedText, Chr$(32))

If MyPosition = 0 Then
ParsedText = Trim(Mid(PassedText, 1))
Me.MiddleName = ParsedText
Else
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.MiddleName = ParsedText
MyPosition = MyPosition + 1
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Suffix
If Not IsNull(Trim(PassedText)) Then Me.Suffix =
Trim(PassedText)
End If
End If
End If
End If


RayToddJr said:
After reading Dale's input, I created the following code to breakdown the
combined name into individual fields.

I am welcoming any feedback that anyone may see with how I have it written.
Everyone is welcome to use and modify as needed for their own use.

Thanks,

Ray.



Dim PassedText As String
Dim ParsedText As String

Dim MyPosition As Long
Dim mylength As Long


'Verify that the Openargs Passed Data
If IsNull(PassedText) Then
Exit Sub
Else

PassedText = Me.OpenArgs
Me.txtpassedtext = PassedText
MyPosition = 0

'Test to see if there is a comma in the passed text. If there isn't one
this is
'a company and should go into the LastName field.

MyPosition = InStr(1, PassedText, ",")
If MyPosition = 0 Then
Me.LastName = PassedText
Else
'Seeking the Last Name.
MyPosition = 0
MyPosition = InStr(1, PassedText, ",")
MyPosition = MyPosition - 1
ParsedText = Trim(Left(PassedText, MyPosition))
Me.LastName = ParsedText
ParsedText = ""

'Moves the starting position to the first letter of the First
Name, ignoring the comma.
MyPosition = MyPosition + 2

'Seeking the First Name.
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
MyPosition = InStr(1, PassedText, Chr$(32))
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.FirstName = ParsedText
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Middle Name.

MyPosition = InStr(1, PassedText, Chr$(32))

If MyPosition = 0 Then
ParsedText = Trim(Mid(PassedText, 1))
Me.MiddleName = ParsedText
Else
ParsedText = Trim(Mid(PassedText, 1, MyPosition))
Me.MiddleName = ParsedText
MyPosition = MyPosition + 1
PassedText = Trim(Mid(PassedText, MyPosition))
MyPosition = 0
ParsedText = ""

'Seeking the Suffix
If Not IsNull(Trim(PassedText)) Then Me.Suffix =
Trim(PassedText)
End If
End If
End If










RayToddJr said:
I have a form that contains a combo box (cboDefendantsName) that contains the
following field:

DefendantName: [LastName] & (", "+[FirstName] & " " & [MiddleName] & " " &
[Suffix])

If the user enters a name that isn't in the list, it will open dataentry
form to allow the user to put this information in the table.

When the data entry form is opened, I would like the information to be
transferred from the originating form via openargs. Once the data is in the
data entry form, I would like for it to be broken down into the four fields
so as to keep the user from having to enter the information twice (one in the
original form and second n the data entry form).

The data would need to be parsed into

LastName
FirstName
MiddleName
Suffix

Keep in mind that the lastname field is also used as a company name and
therefore may not have a "," after it.

Can anyone please share with me on how to parse this field?

Thanks,

Ray.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using VBA, How Can I Replace A Character In A Field? 4
Access 2000 Delete Field info after a comma 6
Text parsing 3
Getting Multiple Owners Names for Single Pieces of Property 8
Add Login Name and Date/Time Stamp to Table 2
Contact First & Last names the wrong way around 6
string function to split name field into 2 seperate fields 1
Parsing a Field 4

Top