0

I need to update a column in access where I should be removing the 7 leading characters in a field.Example:
Column value before: AB34567213. Colum value after: 213
So, I tried using VB in Access and this is what I got so far:

Private Sub delete_leading_chars()
Update table_name
Set col_name = Right(col_name, Len(col_name) - 7)
End Sub

Is there a way to insert this module just like excel where you insert functions in a field to update it/do a calculation on it? In other words, where do I put it in order to run it? If that’s not possible, how should I go about updating that field given the above module? Any idea that will steer me in the right direction is much appreciated!!

2
Contributors
4
Replies
7
Views
6 Years
Discussion Span
Last Post by ruba_cti
0

Why use VBA? This is a pretty simple query, which can run as a query and update or show or whatever.
You can create a new query from Query Design and instead of selecting tables and fields, switch to SQL view and put in

Update table_name
Set col_name = Right(col_name, Len(col_name) - 7)

Save it and you have an update query ready to delete the first 7 characters.
Please note that without criteria, every time this query runs will remove 7 characters from all records, so be carefull.

0

Thanks adam_k for your help!!
The problem that I will run into is that I'm first copying one column's data, let's say col1, into another, col2, and then deleting the first seven characters from col2-- I need to keep col1's data.
So as you said, when I will first run this query, it will delete the first seven characters from col2. But if I run it for the second time -- and even for the following runs -- it will wipe out all the data from this column.
I'm trying to write a Transaction where I first update the table and copy the data and then delete the first 7 characters but it's not working. When I save it/run it as a query it gives me an error "Invalid SQL Statement"
Any idea how to write the transaction?
Here's what I have:

BEGIN TRANSACTION
UPDATE table_name SET col2 = col1
UPDATE table_name SET col2 = Right(col2, Len(col2) - 7)
COMMIT
1

I would use something similar to :

update table_name set col2 = right(col1,len(col1)-7)
where col2 is null

Not only you don't need to set col2 = col1, but you don't have to update the whole table. If col2 doesn't get a value until this query runs, then there should be no problem.
Even if you don't want the where part, then you should be OK if you run this for all records in the table.

Votes + Comments
offered a very efficient and neat way of solving my problem. All promptly
0

adam_k,
That's a very neat way of doing this :)
Thanks a lot!!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.