Seperating Text in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing a field from another database. The field has data that
represents location district compan
and looks like xxxxxxxx.xxxxxxxx (ex.1
or xxxxxxxx.xxxxxxxx.xxxxxxxx (ex. 2
where the x's may be 2-20 characters. The text is sperated by periods (.
I need to seperate these into seperate fields.
I have used various Left, Mid and Right functions to seperate them based on the periods
I have not been able to figure out how to get it to recoginize when a location is missing and fill in only the district and company fields (ex. 1) instead it fills in the location and district fields.
There has to be an easier way than going thru and editing 160K records
Thanks
Harry
 
HJHodge said:
I am importing a field from another database. The field has data that
represents location district company
and looks like xxxxxxxx.xxxxxxxx (ex.1)
or xxxxxxxx.xxxxxxxx.xxxxxxxx (ex. 2)
where the x's may be 2-20 characters. The text is sperated by periods (.)
I need to seperate these into seperate fields.
I have used various Left, Mid and Right functions to seperate them based on the periods.
I have not been able to figure out how to get it to recoginize when a
Click to expand...
location is missing and fill in only the district and >company fields (ex.
1) instead it fills in the location and district fields.

You mention "it" as if everyone knows what "it" is. What is "it"?

When there is data missing is it always the location field? If so, when
there are two
periods you have all three fields and when you have one it is district and
company.

Are the two examples above the only two variations?
 
I am importing a field from another database. The field has data
that represents location district company and looks like
xxxxxxxx.xxxxxxxx (ex.1) or
xxxxxxxx.xxxxxxxx.xxxxxxxx (ex. 2) where the x's may be 2-20
characters. The text is sperated by periods (.) I need to seperate
these into seperate fields. I have used various Left, Mid and
Right functions to seperate them based on the periods. I have not
been able to figure out how to get it to recoginize when a location
is missing and fill in only the district and company fields (ex. 1)
instead it fills in the location and district fields. There has to
be an easier way than going thru and editing 160K records. Thanks,
Harry
Click to expand...

Which version of Access are you using?
What if one period is missing?
How do you know whether the missing data should be a location, or
a district, or a company?

What if the Company name includes a period, Joe Smith, Inc.?
 
rkc, fredg
Sorry.... "It" refers to the query. I have not been able to get the query to recoginize when the location is missing (which is expected in some cases). The results have been the district ends up in the location field and the company ends up in the district field and the company field is blank for district sites which have no location instead of the location field being blank

Access 200
The Ex. 1 format which is missing the location is always a district and company
Example we have district locations like
Chicago.company (xxxxxx.xxxxxx
and each district also has locations like
Peoria.Chicago.company (xxxxxx.xxxxxx.xxxxx
As you can see by the examples the location and districts are both city names so having a location for a district is redundant. The information was extracted from an LDAP directory service.
These are the only 2 variations

Thanks
Harr

----- rkc wrote: ----


HJHodge said:
I am importing a field from another database. The field has data tha
represents location district compan
and looks like xxxxxxxx.xxxxxxxx (ex.1
or xxxxxxxx.xxxxxxxx.xxxxxxxx (ex. 2
where the x's may be 2-20 characters. The text is sperated by periods (.
I need to seperate these into seperate fields
I have used various Left, Mid and Right functions to seperate them base on the periods
I have not been able to figure out how to get it to recoginize when
Click to expand...
location is missing and fill in only the district and >company fields (ex
1) instead it fills in the location and district fields

You mention "it" as if everyone knows what "it" is. What is "it"

When there is data missing is it always the location field? If so, whe
there are tw
periods you have all three fields and when you have one it is district an
company

Are the two examples above the only two variations
 
rkc, fredg Sorry.... "It" refers to the query. I have not been
able to get the query to recoginize when the location is missing
(which is expected in some cases). The results have been the
district ends up in the location field and the company ends up in
the district field and the company field is blank for district
sites which have no location instead of the location field being
blank.

Access 2003 The Ex. 1 format which is missing the location is
always a district and company. Example we have district locations
like Chicago.company (xxxxxx.xxxxxx) and each district also has
locations like Peoria.Chicago.company (xxxxxx.xxxxxx.xxxxx) As you
can see by the examples the location and districts are both city
names so having a location for a district is redundant. The
information was extracted from an LDAP directory service. These
are the only 2 variations.

Thanks, Harry

----- rkc wrote: -----




location is missing and fill in only the district and >company
fields (ex. 1) instead it fills in the location and district
fields.

You mention "it" as if everyone knows what "it" is. What is "it"?

When there is data missing is it always the location field? If so,
when there are two periods you have all three fields and when you
have one it is district and company.

Are the two examples above the only two variations?
Click to expand...

The below will parse the data you have given, PROVIDED THERE IS NO
LOCATON, CITY, OR COMPANY WITH A PERIOD IN THE NAME i.e. St. Louis,
St. Petersburg, St. Paul, etc.
The period is NOT a good column delimiter.
It would be better to use the "|".

Here are 3 functions that will separate your example into 3 different
fields, in a query, using Access 2000 or later. It presupposes that
any missing data will ALWAYS be the location.

Here is the query SQL:

SELECT YourTable.CombinedNames, ParseLocation([CombinedNames]) AS
Location, ParseCity([CombinedNames]) AS City,
ParseCompany([CombinedNames]) AS Company FROM YourTable WHERE
(((YourTable.CombinedNames) Is Not Null));

Change the above table and Field names as needed.

In a module, paste the following 3 functions.

Public Function ParseLocation(FieldIn)
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim Location As String

intX = InStr(1, FieldIn, ".")
Do While intX <> 0
intZ = intX
intY = intY + 1
intX = InStr(intX + 1, FieldIn, ".")
Loop

If intY = 1 Then
Location = "Not Given"
Else
Location = Left(FieldIn, InStr(1, FieldIn, ".") - 1)

End If
ParseLocation = Location
End Function
====================
Public Function ParseCity(FieldIn)
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim City As String

intX = InStr(1, FieldIn, ".")
Do While intX <> 0
intZ = intX
intY = intY + 1
intX = InStr(intX + 1, FieldIn, ".")
Loop

If intY = 2 Then
City = Mid(FieldIn, InStr(1, FieldIn, ".") + 1, (intZ - 1) -
InStr(1, FieldIn, "."))
Else
intX = InStr(1, FieldIn, ".")
City = Left(FieldIn, intX - 1)
End If
ParseCity = City
End Function
==============
Public Function ParseCompany(FieldIn)

ParseCompany = Mid(FieldIn, InStrRev(FieldIn, ".") + 1)

End Function
=============

You can change
Location = "Not Given"
to
Location = ""
if you simply want to leave it blank.
 
Thanks Fred
No ".'s" in any of the names. While I recognize the commands I am not sure I could have ever gotten them put together this way. I will use this opportunity to review how it works and should learn several things.
I really didn't have a choice in the delimiter as it is an output from another program. Now however it looks like I can automate all of the Access process and most of the external process.
I really appreciate the time it took you to do the attached
Thanks again
Harr

----- fredg wrote: ----

rkc, fredg Sorry.... "It" refers to the query. I have not bee
able to get the query to recoginize when the location is missin
(which is expected in some cases). The results have been th
district ends up in the location field and the company ends up i
the district field and the company field is blank for distric
sites which have no location instead of the location field bein
blank.
always a district and company. Example we have district location
like Chicago.company (xxxxxx.xxxxxx) and each district also ha
locations like Peoria.Chicago.company (xxxxxx.xxxxxx.xxxxx) As yo
can see by the examples the location and districts are both cit
names so having a location for a district is redundant. Th
information was extracted from an LDAP directory service. Thes
are the only 2 variations.
location is missing and fill in only the district and >compan
fields (ex. 1) instead it fills in the location and distric
fields.
when there are two periods you have all three fields and when yo
have one it is district and company.
Click to expand...

The below will parse the data you have given, PROVIDED THERE IS N
LOCATON, CITY, OR COMPANY WITH A PERIOD IN THE NAME i.e. St. Louis
St. Petersburg, St. Paul, etc.
The period is NOT a good column delimiter.
It would be better to use the "|".

Here are 3 functions that will separate your example into 3 differen
fields, in a query, using Access 2000 or later. It presupposes tha
any missing data will ALWAYS be the location.

Here is the query SQL:

SELECT YourTable.CombinedNames, ParseLocation([CombinedNames]) A
Location, ParseCity([CombinedNames]) AS City
ParseCompany([CombinedNames]) AS Company FROM YourTable WHER
(((YourTable.CombinedNames) Is Not Null));

Change the above table and Field names as needed

In a module, paste the following 3 functions.

Public Function ParseLocation(FieldIn
Dim intX As Intege
Dim intY As Intege
Dim intZ As Intege
Dim Location As Strin

intX = InStr(1, FieldIn, "."
Do While intX <>
intZ = int
intY = intY +
intX = InStr(intX + 1, FieldIn, "."
Loo

If intY = 1 The
Location = "Not Given
Else
Location = Left(FieldIn, InStr(1, FieldIn, ".") - 1)

End If
ParseLocation = Location
End Function
====================
Public Function ParseCity(FieldIn)
Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim City As String

intX = InStr(1, FieldIn, ".")
Do While intX <> 0
intZ = intX
intY = intY + 1
intX = InStr(intX + 1, FieldIn, ".")
Loop

If intY = 2 Then
City = Mid(FieldIn, InStr(1, FieldIn, ".") + 1, (intZ - 1) -
InStr(1, FieldIn, "."))
Else
intX = InStr(1, FieldIn, ".")
City = Left(FieldIn, intX - 1)
End If
ParseCity = City
End Function
==============
Public Function ParseCompany(FieldIn)

ParseCompany = Mid(FieldIn, InStrRev(FieldIn, ".") + 1)

End Function
=============

You can change
Location = "Not Given"
to
Location = ""
if you simply want to leave it blank.
 
Back
Top