how split column by a delimiter in access?

[dee]

i have two tables i need to compare. One of them has over 300,000 lines of
data. The first table that I need to use for the base has a column named
OWNER and its contents: JSMITH22. The second table that I need to compare
this one to has the OWNER column but its contents are: Smith, john
-(JSMITH22). My thought was to split the column to remove everything but the
JSMITH22 so I can do a compare but I have no idea on how to remove out the
extra characters. keeping in mind there are 300.000 lines of data and each
owner name could have many characters.

 

[Douglas J. Steele]

What's in front of the dash will be Left([MyField], InStr([MyField], "-") -
1).

What's after the dash will be Mid([MyField], InStr([MyField], "-") + 1)

 

[dee]

thanks. Its been so long since I've written a query .. i had a brainn lapse.
worked great. Now I just have to remove out the parthenses.. thanks much

What's in front of the dash will be Left([MyField], InStr([MyField], "-") -
1).

What's after the dash will be Mid([MyField], InStr([MyField], "-") + 1)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i have two tables i need to compare. One of them has over 300,000 lines of
data. The first table that I need to use for the base has a column named
OWNER and its contents: JSMITH22. The second table that I need to compare
this one to has the OWNER column but its contents are: Smith, john
-(JSMITH22). My thought was to split the column to remove everything but
the
JSMITH22 so I can do a compare but I have no idea on how to remove out the
extra characters. keeping in mind there are 300.000 lines of data and each
owner name could have many characters.

 

[John Spencer]

You might try using a non-equi join on the tables. The following is an
alternative to building a new field and populating it with data. It may not
be the best solution for you.

SELECT *
FROM Table2 INNER JOIN Table1
ON Table2.OWNER LIKE "*(" & Table1.OWNER & ")*"

If Table2.Owner always ends at the ")" then you could change the join to
ON Table2.OWNER LIKE "*(" & Table1.OWNER & ")"

This type of query cannot be built in the query grid, however you can build
the query in the grid and join owner to owner. Once you have done that,
switch the view to the SQL view (menu: view: SQL) and edit the join from
ON Table2.OWNER = Table1.OWNER
to
ON Table2.OWNER LIKE "*(" & Table1.OWNER & ")"

Another way to handle this if the number of records in the tables is small
is to add both tables to the query with NO join and then apply criteria
between the fields.
Field: OWNER
Table: Table2
Criteria: LIKE "*(" & Table1.OWNER & ")"

But this can be quite slow since the database engine will have to generate
one record for each combination of records. For example, if table1 has 500
records and table2 has 1000 records then the database engine will generate
500,000 rows before it can do any comparisons.

Your best solution may be to generate a new field in Table2. Especially if
you need to do multiple operations with the data. Douglas Steele has helped
you there.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..