Finding the space in a text string and returning text on either si

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
 
If you ALWAYS have City Name space and State code then it is simple

StateCode: Right([Your Field],2)
CityName: Trim(Left([Your Field],Len([YourField])-2))

Using InstrRev
CityName: Left("Las Vegas NV",InStrRev("Las Vegas NV"," ")-1)
StateCode: Mid("Las Vegas NV",InStrRev("Las Vegas NV"," ")+1)

It gets a little more complex if you have fields that are blank or have just a
city name or have no spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
jim said:
I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
Click to expand...


That should work except for the space being in the result.
If the space is what you mean by "can't seem to get what is
needed", then use:
Left([ADDR3],InStrRev([ADDR3]," ",-1,1) - 1)

OTOH, if you have entries that do not have a space (maybe a
comma or no state), then that will produce an error. Then
it gets messy because you have to check for whatever other
situations you actually have in the Addr3 field.
 
John,
Thank you for your help, but of course, it can't be that simple. The field
in the table named ADDR3 is a text field and is 28 characters in length, and
it seems to be left justified. If ADDR3 has Las Vegas NV and the function is-
City: Left([ADDR3],InStrRev([ADDR3]," ")-1), it returns Las Vegas NV instead
of Las Vegas.

I also tried using the InStr function as follows: City:
Left([ADDR3],InStr([ADDR3]," ")-1), which works if the city is one word, i.e.
Reno, but if the city is Las Vegas, it only returns Las.

I'm ready for more thoughts. Thank you again.


John Spencer said:
If you ALWAYS have City Name space and State code then it is simple

StateCode: Right([Your Field],2)
CityName: Trim(Left([Your Field],Len([YourField])-2))

Using InstrRev
CityName: Left("Las Vegas NV",InStrRev("Las Vegas NV"," ")-1)
StateCode: Mid("Las Vegas NV",InStrRev("Las Vegas NV"," ")+1)

It gets a little more complex if you have fields that are blank or have just a
city name or have no spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
Click to expand...
.
Click to expand...
 
Ok It sounds as if Addr3 is a fixed length string and is padded with spaces.
If that is true, we can work around it by using the Trim function to remove
the extraneous spaces.

Using InstrRev the following should work
CityName: Left(Addr3,InStrRev(Trim(Addr3)," ")-1)
StateCode: Mid(Trim(Addr3),InStrRev(Trim(Addr3)," ")+1)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John,
Thank you for your help, but of course, it can't be that simple. The field
in the table named ADDR3 is a text field and is 28 characters in length, and
it seems to be left justified. If ADDR3 has Las Vegas NV and the function is-
City: Left([ADDR3],InStrRev([ADDR3]," ")-1), it returns Las Vegas NV instead
of Las Vegas.

I also tried using the InStr function as follows: City:
Left([ADDR3],InStr([ADDR3]," ")-1), which works if the city is one word, i.e.
Reno, but if the city is Las Vegas, it only returns Las.

I'm ready for more thoughts. Thank you again.


John Spencer said:
If you ALWAYS have City Name space and State code then it is simple

StateCode: Right([Your Field],2)
CityName: Trim(Left([Your Field],Len([YourField])-2))

Using InstrRev
CityName: Left("Las Vegas NV",InStrRev("Las Vegas NV"," ")-1)
StateCode: Mid("Las Vegas NV",InStrRev("Las Vegas NV"," ")+1)

It gets a little more complex if you have fields that are blank or have just a
city name or have no spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
Click to expand...
.
Click to expand...
Click to expand...
 
Marshall, Thank you for helping. I tried the different variation you
suggested, but it returned an error.


Marshall Barton said:
jim said:
I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
Click to expand...


That should work except for the space being in the result.
If the space is what you mean by "can't seem to get what is
needed", then use:
Left([ADDR3],InStrRev([ADDR3]," ",-1,1) - 1)

OTOH, if you have entries that do not have a space (maybe a
comma or no state), then that will produce an error. Then
it gets messy because you have to check for whatever other
situations you actually have in the Addr3 field.
Click to expand...
 
jim said:
Thank you for your help, but of course, it can't be that simple. The field
in the table named ADDR3 is a text field and is 28 characters in length, and
it seems to be left justified. If ADDR3 has Las Vegas NV and the function is-
City: Left([ADDR3],InStrRev([ADDR3]," ")-1), it returns Las Vegas NV instead
of Las Vegas.

I also tried using the InStr function as follows: City:
Left([ADDR3],InStr([ADDR3]," ")-1), which works if the city is one word, i.e.
Reno, but if the city is Las Vegas, it only returns Las.
Click to expand...


The only way Left([ADDR3],InStrRev([ADDR3]," ")-1) can
return Las Vegas NV is if there is a space after NV

Since Access goes out of its way to remove trailing spaces,
its not easy to get them. Importing from another system is
the most common explanation.

IF that's what's happening in your case, try:

Left(Trim([ADDR3]),InStrRev(Trim([ADDR3])," ")-1)

If that does not make a difference, then I am at a loss to
explain why it doesn't do what you want.
 
John,

Works perfectly! I learned a lot today!!..Thank you so much and a thank you
goes to Marshall as well. I should have know to use the trim function. Makes
sense. Again, many thanks.

John Spencer said:
Ok It sounds as if Addr3 is a fixed length string and is padded with spaces.
If that is true, we can work around it by using the Trim function to remove
the extraneous spaces.

Using InstrRev the following should work
CityName: Left(Addr3,InStrRev(Trim(Addr3)," ")-1)
StateCode: Mid(Trim(Addr3),InStrRev(Trim(Addr3)," ")+1)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John,
Thank you for your help, but of course, it can't be that simple. The field
in the table named ADDR3 is a text field and is 28 characters in length, and
it seems to be left justified. If ADDR3 has Las Vegas NV and the function is-
City: Left([ADDR3],InStrRev([ADDR3]," ")-1), it returns Las Vegas NV instead
of Las Vegas.

I also tried using the InStr function as follows: City:
Left([ADDR3],InStr([ADDR3]," ")-1), which works if the city is one word, i.e.
Reno, but if the city is Las Vegas, it only returns Las.

I'm ready for more thoughts. Thank you again.


John Spencer said:
If you ALWAYS have City Name space and State code then it is simple

StateCode: Right([Your Field],2)
CityName: Trim(Left([Your Field],Len([YourField])-2))

Using InstrRev
CityName: Left("Las Vegas NV",InStrRev("Las Vegas NV"," ")-1)
StateCode: Mid("Las Vegas NV",InStrRev("Las Vegas NV"," ")+1)

It gets a little more complex if you have fields that are blank or have just a
city name or have no spaces.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

jim wrote:
I have a field that has both city and state. The state is abbrievated with 2
characters. Of course, I am trying to seperate the two, so I need one field
of my query to find the space and return the text left of the space and the
next field of the query to find the text to the right. If tried using the
InStrRev function, but can't seem to get what is needed. Following is a copy
of the statement I am using. Any help will be apprecaited.

Left([ADDR3],InStrRev([ADDR3]," ",-1,1))
.
Click to expand...
Click to expand...
.
Click to expand...
 
Try using the SPLIT function. Below code tests for blanks/spaces and/or
commas, i.e.

Las Vegas, NV
Las Vegas NV

==========================

Dim aTemp() As String
Dim strState As String
Dim strCity As String
Dim i As Integer

If InStr(Me.txtInputString.Value, ",") > 0 Then
aTemp = Split(Me.txtInputString.Value, ",")
Else
aTemp = Split(Me.txtInputString.Value, " ")
End If

strState = aTemp(UBound(aTemp))
For i = 0 To UBound(aTemp) - 1
strCity = strCity & " " & aTemp(i)
Next i

MsgBox "City: " & strCity & vbCrLf & "State: " & strState

==========================
 
Back
Top