Update Right & Left Columns

One of our Access users has a question he has posed, for which we are still
struggling to answer.

Let's see if I can explain his goal as clearly as you would need:

He has a table in Access, with many columns and hundreds of records.

One of the columns in the table contains data that is now mostly obsolete.
The first two columns are titled LEFT and RIGHT. The data in each record for
LEFT and RIGHT should consist of the following data: The LEFT field should
contain the leftmost character in column four of the same table, and the
RIGHT field should contain the rightmost character in column four of the
table that is associated with that specific record. As soon as the LEFT and
RIGHT columns contain the relavant data, column four can be deleted.

He does not want to manually type this data into the two columns. The
character set is the entire range of viewable characters including special
charcters such as # and $, underscore and so on. We have tried using VB to
write a routine, but I am unable to figure out how to run the macro so that
it will increment records (or rows) after inserting the characters in the
first record. Selecting the leftmost and rightmost character, copying it,
then pasting it into the other field is not working well either. So, I'm not
sure a macro solution is going to work - but that may just be due to my lack
of experience in writing VB macros for Access.

Thanks in advance for your help.

Hi.

You can use an update query to set the values in the correct columns. By
the way, "Right" and "Left" are Reserved keywords because they are functions
in Visual Basic, so using them to name columns isn't a good idea, especially
since the update query that makes this change is so quick and easy uses these
functions.

UPDATE MyTable
SET LeftChar = Left(SomeColumn, 1),
RightChar = Right(SomeColumn, 1);

I would recommend naming these columns something more descriptive, such as
CostCode, or whatever name would easily identify each column, so that future
programming maintenance will be easier. After the update query is finished,
it's probably just easiest for your user to open the table in Design View and
delete the fourth column, then save the table, but a query could be used to
drop the column, instead.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.


"G. Edwards" wrote:

One of our Access users has a question he has posed, for which we are still
struggling to answer.

Let's see if I can explain his goal as clearly as you would need:

He has a table in Access, with many columns and hundreds of records.

One of the columns in the table contains data that is now mostly obsolete.
The first two columns are titled LEFT and RIGHT. The data in each record for
LEFT and RIGHT should consist of the following data: The LEFT field should
contain the leftmost character in column four of the same table, and the
RIGHT field should contain the rightmost character in column four of the
table that is associated with that specific record. As soon as the LEFT and
RIGHT columns contain the relavant data, column four can be deleted.

He does not want to manually type this data into the two columns. The
character set is the entire range of viewable characters including special
charcters such as # and $, underscore and so on. We have tried using VB to
write a routine, but I am unable to figure out how to run the macro so that
it will increment records (or rows) after inserting the characters in the
first record. Selecting the leftmost and rightmost character, copying it,
then pasting it into the other field is not working well either. So, I'm not
sure a macro solution is going to work - but that may just be due to my lack
of experience in writing VB macros for Access.

Thanks in advance for your help.