?

  • Thread starter Thread starter Shawna
  • Start date Start date
S

Shawna

Is there an easy way to split one field into 2?
I have delimited file to import into my db. It imports
fine with a macro I've set up. What I'd like to know
though is once the data is in can I take Field1 and split
into Field1 and Field2 or Field2 and Field3 (leaving
Field1 the original data)

Field1 looks like this

01000-H
I'd like to have 01000 in one field and the H in a
separte field.
Or Can I do this easily upon import?
Just looking for a quick solution....Thanks to all

Note, this import will happen on an ongoing basis.

thanx in advance....Shawna
 
Hi... one simple way to do this is to export the current
database to Excel and use the LEFT() and RIGHT()
functions, and then copy the data back to Access.

Hope this helps -
Eliezer
 
Hi Shawna,

Add the new fields to your table. Make them both text fields (otehrwise
you'll lose the leading zeros in things like 01000.

Then create a query on your table, an ordinary Select query. For the
first field in the query, use something like
Field2: Left([Field1], 4)
and for the second
Field3: Right([Field1], 1)

Preview the query to make sure it's splitting the data the way you want.
When it's right, use the commands on the Query menu to convert it into
an update query, and set Field2 in the query to update Field2 in the
table, and so on.

Finally, run the update query.
 
There are a few ways that you can do this from within
access.

The first thing that I would look at is whether your data
is fixed length. Some of the text files that I import
are comma delimited, but I actually import them as fixed
length because each field actually is a fixed length and
this is a little more reliable (avoids problems with
embedded quotes). This makes it very easy to parse
fields (if the parse location is always the same). It's
a little bit of work to set all of the field boundaries,
but you can save them so you only have to do it once.

If this will not work for you, I would consider the
following.

1. Add two new fields to your imported table to accept
the parsed data.
2. Create an update query and use the left() and right()
functions to calculate the new field values.
3. Run the update query.
4. Verify that the parse ran correctly.
4. Delete the original field from the table (optional,
but there is really no need for it since it can always be
recreated by adding the two new fields).

Of course, this assumes that the length of the left and
right portions that you want to parse are always the same
length. If they are not, you can use the Instr() and Len
() functions within the left() and right() funtions to
return the values left and right of the dash.

Hope that helps.

-Ted
 
Thank you all for your help!
Appreciate it as usual....
Shawna
-----Original Message-----
Hi Shawna,

Add the new fields to your table. Make them both text fields (otehrwise
you'll lose the leading zeros in things like 01000.

Then create a query on your table, an ordinary Select query. For the
first field in the query, use something like
Field2: Left([Field1], 4)
and for the second
Field3: Right([Field1], 1)

Preview the query to make sure it's splitting the data the way you want.
When it's right, use the commands on the Query menu to convert it into
an update query, and set Field2 in the query to update Field2 in the
table, and so on.

Finally, run the update query.



Is there an easy way to split one field into 2?
I have delimited file to import into my db. It imports
fine with a macro I've set up. What I'd like to know
though is once the data is in can I take Field1 and split
into Field1 and Field2 or Field2 and Field3 (leaving
Field1 the original data)

Field1 looks like this

01000-H
I'd like to have 01000 in one field and the H in a
separte field.
Or Can I do this easily upon import?
Just looking for a quick solution....Thanks to all

Note, this import will happen on an ongoing basis.

thanx in advance....Shawna

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
 
Back
Top