Delete the first 18 characters in each Field1

[Guest]

I need a query that runs through my table and deletes the first 18 characters
in field1 leaving the remaining characters as the new value of field1.

 

[Tom Ellison]

Dear Julia:

Use:

Mid(YourColumnNameGoesHere, 19)

This will return everything from the 19th character onward. It will error
if there are fewer than 19 characters. Will that be a problem?

Tom Ellison

 

[Guest]

I'm new to Access and I need a little more direction than this.

No, it isn't a problem if it errors.

 

[Tom Ellison]

Dear Julia:

Possibly the easiest way to help you is this. Create a query that updates
this field to its current value, not making any change. Post the SQL View
of that query here, and I'll modify that so you can accomplish what you
want.

Make very certain you create a copy of the database containing the table
involved, and work from that. If the update query malfunctions while you
are working on it, you'll need to be able to restore things before you test
again. Also, you mustn't destroy the "live" data.

Tom Ellison

 

[Guest]

SELECT recvfile.Field1
FROM recvfile;

Dear Julia:

Possibly the easiest way to help you is this. Create a query that updates
this field to its current value, not making any change. Post the SQL View
of that query here, and I'll modify that so you can accomplish what you
want.

Make very certain you create a copy of the database containing the table
involved, and work from that. If the update query malfunctions while you
are working on it, you'll need to be able to restore things before you test
again. Also, you mustn't destroy the "live" data.

Tom Ellison

 

[Tom Ellison]

Dear Julia:

UPDATE recvfile
SET Field1 = Mid(Field1, 19)
WHERE Len(Field1) > 18

The third line above is to avoid the error that would prevent the query from
functioning if there are any rows in which Field1 is not at least 19
characters long.

Again, DO NOT RUN THIS ON YOUR ONLY COPY OF THE DATA. Very important. If
you destroy your information, please do not blame me. I have warned you!
Very important!

Tom Ellison

 

[fredg]

SELECT recvfile.Field1
FROM recvfile;

Back up your data first.
Create a new Update query.

Update recvfile Set recvfile.Field1 = IIf(Len([Field1]) >=19,
Mid([Field1],19),[Field1]);

The above will save only the data from the 19th character on, or if
the length of the field is less than 19 characters the entire field as
is.