First and last name button? 2

[bricklebrit]

Hello,
I am redoing some of my main data and seperating the Customer information into first and last name and then street address in seperate fields.

Right now, I have the field [CustomerName], I'm trying to build a button that, when clicked, will automatically put the first and last name into the corresponding fields:

[CustomerFirstName]
[CustomerLastName]

In a report in the past, I've used the stock code:
Trim(Left([CustomerName],InStr(1,[CustomerName]," &quot-1

and

Trim(Right([CustomerName],InStr(1,[CustomerName]," &quot-2

but I am having difficulty with the syntax in programming a button to assign these values to new field values.

Thanks in advance for any assitance or advice!

 

[scriverb]

First of all I believe that your stock code is incorrect in stripping off the first and last names. I have corrected the code so that it works for names stored as firstname, space, lastname(i.e. Robert Smith) It would have to be modified if you also have Middle Initials and Suffix's(i.e. Jr., III, IV, etc.) Also UpperCase, LowerCase, or StrConv functions could be used to format the strings as you like them. Here are three possible ways to do what you want.

1. In the OnClick event procedure of the button put the following code:

This code puts the Last and then the first name in corresponding Form objects. If the Form objects are bound to the fields in your table then the fields are being updated.

Me![[CustomerFirstName] = Mid$(Me![CustomerName],1,Instr(1,me![CustomerName]," &quot-1)
Me![CustomerLastName] = Trim(Right(Me![CustomerName],Len(Me![CustomerName])-InStr(1,Me![CustomerName]," &quot))

2. If you are just performing an update of the fields of the table with a utility form with a button on it then your would put the following in the OnClick procedure.

Dim db as database
Dim rs as recordset
Set db = currentdb
Set rs = db.openrecordset("tblYourTableName", dbOpenDynaset)
rs.movefirst
Do
rs.edit
rs("CustomerLastName&quot = Mid$(rs("CustomerName&quot,1,Instr(1,rs("CustomerName&quot," &quot-1)
rs("CustomerFirstName&quot = Trim(Right(rs("CustomerName&quot,Len(rs("CustomerName&quot)-InStr(1,rs("CustomerName&quot," &quot))
rs.update
rs.movenext
loop until rs.eof
rs.close
db.close

3. If you want to use a query to update your table you can paste the following SQL into the SQL property of a query and run:

UPDATE tblYourTableName SET tblYourTableName.CustomerFirstName = Mid$([tblYourTableName]![CustomerName],1,InStr(1,[tblYourTableName]![CustomerName]," &quot-1), tblYourTableName.CustomerLastName = Trim(Right([tblYourTableName]![CustomerName],Len([tblYourTableName]![CustomerName])-InStr(1,[tblYourTableName]![CustomerName]," &quot));

If you need additional help please come back with more questions.

Bob Scriver

 

[bricklebrit]

Hi Bob,

Thanks for the extremely prompt and thorough answer! I ended up using the working code:

Private Sub CutupName_Click()

Me![CustomerFirstName] = Mid$(Me![CustomerName], 1, InStr(1, Me![CustomerName], " &quot - 1)
Me![CustomerLastName] = Trim(Right(Me![CustomerName], Len(Me![CustomerName]) - InStr(1, Me![CustomerName], " &quot))

End Sub

The code work beautifully -- thanks again!

+++

Once I get all the names split up, what I'm actually trying to do is parse a quickly pasted address and split it up to seperate fields as fast as possible and then make any minor alterations manually.

I've been trying to create a button to parse the an address like:

1234 Homestead
Los Angeles CA 92392

Into fields like:

[ShipAddress1] 1234 Homestead
[ShipCity] Los Angeles
[ShipState] CA
[ShipZip] 92392

Do you know of any stock code that could do such a task? Or if I could at least get it to the point of

Strip line 1 to: [ShipAddress1]
Strip line 2 to: [ShipCity]
Strip last 5 numeral digits of [ShipCity] into [ShipZip] and I could cut in the [ShipState] from there

If you have any thoughts on this, it would be much appreciated!

Thanks again for your assistance!

 

[scriverb]

The Instr function searches for a target string from the left. You need a InstrRight function to search for a string from the right. Paste the following Function into a database module.

Public Function InStrRight(vSearchStr As String, vTargetStr As String) As Long
Dim i As Integer
For i = Len(vSearchStr) To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
End Function

I am assuming that the pasted address will be all hooked together with a carriage return in the string between the Address line and the CityStZip line and there are no spaces in the CityNames. If that is the case then you can use the following pasted into the button click. I am assigning this values to text boxes on the form with your button:

Dim vShipLine2 As String
Me![ShipAddress] = Mid$(Me![PasteAddress], 1, InStr(1, Me![PasteAddress], Chr(13)) - 1)
vShipLine2 = Mid$(Me![PasteAddress], InStr(1, Me![PasteAddress], Chr(13)) + 2)
Me![ShipCity] = Mid$(vShipLine2, 1, InStr(1, vShipLine2, " &quot)
Me![ShipState] = Mid$(vShipLine2, InStr(1, vShipLine2, " &quot + 1, InStrRight(vShipLine2, " &quot - InStr(1, vShipLine2, " &quot)
Me![ShipZip] = Right$(vShipLine2, Len(vShipLine2) - InStrRight(vShipLine2, " &quot)

Try this out. Get back with me to fine tune the adjustments.

Bob Scriver

 

[scriverb]

Sorry, should have included a Trim on both of these statements in case your address had more than one space between City or State or ZipCodes.

Me![ShipState] = Trim(Mid$(vShipLine2, InStr(1, vShipLine2, " &quot + 1, InStrRight(vShipLine2, " &quot - InStr(1, vShipLine2, " &quot))
Me![ShipZip] = Trim(Right$(vShipLine2, Len(vShipLine2) - InStrRight(vShipLine2, " &quot))

Good Luck

Bob Scriver

 

[bricklebrit]

Hi Bob,

HOLEY SCMOLEY -- Thanks for the fast response and quick answer! It works great.

I combined the two codes into a single button:

Private Sub CutupAddressButton_Click()

Me![CustomerFirstName] = Mid$(Me![CustomerName], 1, InStr(1, Me![CustomerName], " &quot - 1)
Me![CustomerLastName] = Trim(Right(Me![CustomerName], Len(Me![CustomerName]) - InStr(1, Me![CustomerName], " &quot))

If ([Country]) = "USA" Then

Dim vShipLine2 As String
Me![ShipAddress1] = Mid$(Me![Address], 1, InStr(1, Me![Address], Chr(13)) - 1)
vShipLine2 = Mid$(Me![Address], InStr(1, Me![Address], Chr(13)) + 2)
Me![ShipCity] = Mid$(vShipLine2, 1, InStr(1, vShipLine2, " &quot)
Me![ShipState] = Trim(Mid$(vShipLine2, InStr(1, vShipLine2, " &quot + 1, InStrRight(vShipLine2, " &quot - InStr(1, vShipLine2, " &quot))
Me![ShipZip] = Trim(Right$(vShipLine2, Len(vShipLine2) - InStrRight(vShipLine2, " &quot))

End If

End Sub

In terms or refinements, because many City addresses are two words, is it possible for the

[ShipState] to be just last two characters of [ShipCity] instead of that last word in the line (taking into account spaces/removing the spaces):

New York, NY now translates to:

[ShipCity] New
[ShipState] York, NY

Also I don't need the extended zip people often put on their zip codes. Is there a way to shave off any text in the zip that end with -1234 (four digits)?

Thanks again for all your help!

 

[scriverb]

Yes, we can do all of that but I am in the middle of unloading 2 yards of topsoil to my wife's garden and she is screaming to get off the computer and that $#%@& Tek-Tips thing. She just doesn't understand.

I will get back with you later this afternoon after my yard responsibilities are done.

Quick question. Are all of the CityStateZip lines broken up with a comma(,) behind the City? If they are then we can modify this to handle the Cities with spaces real easy. Also, we can trim off the trailing Zip+4 info that is not needed. Just get back with the exact formatting that we should expect for the addresses.

Bob Scriver

 

[bricklebrit]

Hi Bob,

Its not urgent -- I full understand top-soil priority!!

Unfortuanately, its a hodge-podge of entries of people typing in their own address so not everybody uses a comma after the city (although most do).

I guess if we want to get really fancy-schmancy we could:

- do an IF detect to see if they use a comma and then split off the state and zip after that

- if no comma, just cut off the zip and then the last two characters behind that (which would most likely be the two-letter state abbreviation).

Anyway, thanks again for all your assistance. I'll talk to you later on!
Jon

 

[scriverb]

Okay, yard work is done. Are you ready for this? This should take care of all situations.
New code for the OnClick of the button:
Private Sub Command2_Click()
Dim vShipLine2 As String
Me![ShipAddress] = StrConv(Mid$(Me![PasteAddress], 1, InStr(1, Me![PasteAddress], Chr(13)) - 1), 3)
vShipLine2 = Trim(Mid$(Me![PasteAddress], InStr(1, Me![PasteAddress], Chr(13)) + 2))
If (InStr(1, vShipLine2, " &quot > 0) Or (InStr(1, vShipLine2, " &quot > 0) Or (InStr(1, vShipLine2, " &quot > 0) Then
vShipLine2 = StripExtraSpaces(vShipLine2)
End If
Me![ShipCity] = StrConv(Trim(Mid$(vShipLine2, 1, InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1))), 3)
If InStr(1, Me![ShipCity], ",&quot > 0 Then
Me![ShipCity] = StrConv(Mid$(Me![ShipCity], 1, Len(Me![ShipCity]) - 1), 3)
End If
Me![ShipState] = StrConv(Trim(Mid$(vShipLine2, InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1, InStrRight(vShipLine2, " ", Len(vShipLine2)) - (InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1))), 3)
Me![ShipZip] = Trim(Mid$(vShipLine2, InStrRight(vShipLine2, " ", Len(vShipLine2)) + 1, 5))
End Sub

New code for the InstrRight Function:
Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim i As Integer
For i = vStart To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
End Function

Added a Start Position Parameter so that we could indicate a starting position that was something other than the last character of the string.

New Function to strip off extra spaces throughout the string. You said that individuals would be entering all kinds of stuff so this strips off the extra spaces that they might enter. Paste this in at the Forms General area.
Function StripExtraSpaces(vStringValue As String) As String
Dim vPosition As Long
Dim vTargetString As String
vTargetString = " "
DoItAgain:
Do
If InStr(1, vStringValue, vTargetString) > 0 Then
vPosition = InStr(1, vStringValue, vTargetString)
Else
StripExtraSpaces = vStringValue
GoTo Check_DoItAgain
End If
vStringValue = Mid$(vStringValue, 1, vPosition - 1) & " " & Mid$(vStringValue, vPosition + Len(vTargetString))
Loop
Check_DoItAgain:
Select Case Len(vTargetString)
Case 2
GoTo SES_Exit
Case 3
vTargetString = " "
GoTo DoItAgain
Case 4
vTargetString = " "
GoTo DoItAgain
End Select
SES_Exit:
'Exit the function with only single spaces in string
End Function

Now this new code removes extra spaces, handles the cities with spaces in them, handles the cities with a comma(,) behind them and strips off the Zip+4 stuff from the hyphe(-) back.

Sorry, about all the extra code but I really got into this one. It was a challenge and fun to work on.

Good luck and I hope this makes you app run the way you wanted.

Bob Scriver

 

[bricklebrit]

Bob!

One-thousand thank yous! The new code works beautifully. Its just what I wanted and will save me hours of labor of cleaning up people's addresses!! I can't thank you again for all your work!

Jon

 

[scriverb]

Hey, glad you liked the code. It was a pleasure.

One last thought, you might want to change the StrConv function for the State to UPPER so that the STATE abbreviations come through all capitalized. Just a thought.

Bob Scriver

 

[bricklebrit]

Hi Bob,

I would like to have it keep the state in uppercase, but I hated to bother you any more -- is this the part of the code that is determining title case?:

Select Case Len(vTargetString)
Case 2
GoTo SES_Exit
Case 3
vTargetString = " "
GoTo DoItAgain
Case 4
vTargetString = " "
GoTo DoItAgain
End Select

If so, how can I make all the [ShipState] in the form of UPPERCASE?

Thanks,
Jon

 

[scriverb]

No just change the Function to UCase:

Me![ShipState] = UCase(Trim(Mid$(vShipLine2, InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1, InStrRight(vShipLine2, " ", Len(vShipLine2)) - (InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1))))

You will notice that I removed the extra ", 3" at the end of the expression. This is because the UCase function doesn't require a second parameter. The StrConv function requires it to determine what you want to do to the string.

vbProperCase 3 Converts the first letter of every word in string to uppercase.
Check out StrConv in ACCESS Help We could have used StrConv with a second parameter of 1 to get UPPER case characters only. Same as UCase.

BTW: The code that you were asking about in your previous post was to strip the extra spaces that might be in the string from the user input. It will strip up to 4 spaces out and replace them with a single space before we started the parsing process. Had to have it down to single spaces between words in the string to be effective.

And you are not a bother. This project is fun and I will use it myself in the future. I passed it out to my staff as an exercise in string manipulation. It never hurts to solve a problem for the fun of it. Knowledge is trying.

Bob Scriver

 

[bricklebrit]

Hi Bob,

Thanks for the quick fix. It works great now. I really appreciate all your help and work on this -- you really went out of your way!

I'm actually using the split up fields to export to the USPS shipping program to print out shipping labels with delivery confirmation codes.

If you decide you want to spend any more time on this code(although its more than sufficient for my needs with a little checking) a few more fixes for frequent inconsistencies in the way people type in their address are:

* USPS databases don't have any commas or periods so it could purge out all punctuation

* check line 1 - if the it encounters a line with no numbers at the beginning, automatically cut this line into a separate shipping field [ShipAddress2] (such as an address that has a business name or a c/o line in the address)

* check the end of line to see if it encounters any addresses including "#" "Apartment" "Apt" "Suite" "Floor" "Flr" and it could cut these into a separate field, [ShipAddress1]

* also if people don't put any spaces inbetween their city and state, the code doesn't interpret it quite right - such as:
7471 Moose Lodge Lane
Orlando,FL 32819

It doesn't recognize Orlando and FL as separate entries.

Anyway, I'm certainly pleased with code as-is (and now with UPPERCASE STATES!), but I thought I would just list out the common fixes I do manually.

Thanks again, Bob!

Jon

 

[scriverb]

I think this satisfies all of the new requirements.

New code for the OnClick of the button:
Private Sub Command2_Click()
Dim vShipLine2 As String
Me![ShipAddress] = Null
Me![ShipAddress1] = Null
Me![ShipAddress2] = Null
Me![ShipCity] = Null
Me![ShipState] = Null
Me![ShipZip] = Null
Me![PasteAddress] = StripExtraSpaces(Me![PasteAddress])
Me![ShipAddress] = StrConv(Mid$(Me![PasteAddress], 1, InStr(1, Me![PasteAddress], Chr(13)) - 1), 3)
If Mid$(Me![ShipAddress], 1, 1) > Chr(57) Then
Me![ShipAddress2] = Me![ShipAddress]
End If
If InStr(1, Me![ShipAddress], "#&quot > 0 Or Me![ShipAddress] Like "*Apt*" Or Me![ShipAddress] Like "*Apartment*" Or Me![ShipAddress] Like "*Flr*" Or Me![ShipAddress] Like "*Floor*" Or Me![ShipAddress] Like "*Suite*" Or Me![ShipAddress] Like "*Ste*" Then
Me![ShipAddress1] = Me![ShipAddress]
End If
vShipLine2 = Trim(Mid$(Me![PasteAddress], InStr(1, Me![PasteAddress], Chr(13)) + 2))
Me![ShipCity] = StrConv(Trim(Mid$(vShipLine2, 1, InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1))), 3)
Me![ShipState] = StrConv(Trim(Mid$(vShipLine2, InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1, InStrRight(vShipLine2, " ", Len(vShipLine2)) - (InStrRight(vShipLine2, " ", InStrRight(vShipLine2, " ", Len(vShipLine2)) - 1) + 1))), 1)
Me![ShipZip] = Trim(Mid$(vShipLine2, InStrRight(vShipLine2, " ", Len(vShipLine2)) + 1, 5))
End Sub

Replace the function StripExtraSpaces with the following:
Function StripExtraSpaces(vStringValue As String) As String
Dim vPosition As Long
Dim vTargetString As String
vTargetString = " "
DoItAgain:
Do
If InStr(1, vStringValue, vTargetString) > 0 Then
vPosition = InStr(1, vStringValue, vTargetString)
Else
StripExtraSpaces = vStringValue
GoTo Check_DoItAgain
End If
vStringValue = Mid$(vStringValue, 1, vPosition - 1) & " " & Mid$(vStringValue, vPosition + Len(vTargetString))
Loop
Check_DoItAgain:
Select Case Len(vTargetString)
Case 1
If vTargetString = "." Then GoTo SES_Exit
vTargetString = "."
GoTo DoItAgain
Case 2
vTargetString = ","
GoTo DoItAgain
Case 3
vTargetString = " "
GoTo DoItAgain
Case 4
vTargetString = " "
GoTo DoItAgain
End Select
SES_Exit:
'Exit the function with single spaces in string
End Function

Add two new text boxes to your form for [ShipAddress1] and [ShipAddress2]. I put the first line of the address always in [ShipAddress]. Then according to your new requirements I populated the new fields. If the requirements for [ShipAddress1] and [ShipAddress2] are both met that they will both have the [ShipAddress] data in that text box.(i.e. C/O ABC Mfg. Apt. 4 ) No beginning numbers and an Apt designation.

This should take you a step closer to your needs.

Bob Scriver

 

[bricklebrit]

Hi Bob,

Holey ghamoleee... thanks for all the extra coding. Sorry for the delay in responding -- I was away for a few days and didn't have access to the database. It works great!

Thanks again for all your assistance, Bob!
Jon