Split field based upon puntuation mark, not a space

[russi]

Hi.
I have a field City_ST_Zip.
Staff entered a city name followed by a ',' and then the state's name, and finally the zip code. (ex., North Adams, MA 01608)

I need to separate out each field into their own. (i.e., City, State, Zip)

I know how to separate based upon spaces, but where many cities have 2 parts to their name, I need to separate by the comma.

Russ

 

[SonicClang]

Check out the Instr function http://www.techonthenet.com/access/functions/string/instr.php

Possibly?
(InStr(1,[City_ST_Zip],",")

 

[WayneRyan]

russi,

I'd use a Public Function in your query.

City: GetComponent([City_ST_Zip], 1)
State: GetComponent([City_ST_Zip], 2)
Zip: GetComponent([City_ST_Zip], 3)

Public Function GetComponent(WholeField As String, WhichItem As Long) As String
Dim intStart Integer

Select Case WhichItem
   Case 1
     ' [B][SIZE="3"]City [/SIZE][/B]= all up until the ","
     '
     GetComponent = Mid(WholeField, InStr(1, WholeField, ",") - 1
   Case 2
     ' [B][SIZE="3"]State [/SIZE][/B]= Next word after the comma
     '
     intStart = Mid(WholeField, InStr(1, WholeField, ",") + 1
     WholeField = Mid(LTrim(Mid(WholeField, intStart)
     GetComponent = Mid(WholeField, 1, InStr(1, WholeField, " ") - 1)
   Case 3
     ' [B][SIZE="3"]Zip [/SIZE][/B]is the last word
     '
     GetComponent = Mid(WholeField, InStrRev(WholeField, " ") + 1)
   End Select
End Function

hth,
Wayne

 

[Brianwarnock]

I would use the Split function in a public function

Public Function selfield(WholeField As String, fieldnum as Integer) As String

myarray = Split(wholefield, ",")
selfield= myarray(fieldnum - 1)

End Function

Brian

 

[Brianwarnock]

After I had posted realised that I had an example that I had done before, it is more complete so here it is

Function parse(fld As String, reqfld As Integer) As String

fld = Trim(fld) 'remove all blanks around text
fld = Replace(fld, ", ", ",") 'to replace the ,space with just ,
fld = Replace(fld, " ", ",") 'replace all other intenal spaces with

myarray = Split(fld, ",") 'build array
maxvalue = UBound(myarray, 1)
If maxvalue < reqfld - 1 Then
parse = " "
Else
parse = myarray(reqfld - 1) 'Select required field from zero based array
End If

End Function

Looked at example again and realised that this wont work, I see now why Wayne did not use Split.

Brian

 

[Brianwarnock]

Being a stubborn B*%%£r and determined to use Split after lunch I looked at this again and came up with

Function parse2(fld As String, reqfld As Integer) As String

fld = Trim(fld) 'remove all blanks around text
fld = Left(fld, InStrRev(fld, " ") - 1) & "," & Right(fld, Len(fld) - InStrRev(fld, " "))
myarray = Split(fld, ",")  'build array
maxvalue = UBound(myarray, 1)
If maxvalue < reqfld - 1 Then
parse2 = " "
Else
parse2 = myarray(reqfld - 1) 'Select required field from zero based array
End If

End Function

Brian