Remove last character if = "/" ; in a whole datasheet

E

eltokaji

Hi,

we have to transfer datasheets into another program, but just turned
out that several thousands of lines has / sign in the end of the lines
(my mistake). In order to be transferred correctly, this has to be
removed. How con I solve this problem?
So I would need a VBA script to remove the last character only if this
a / sign .
Any help is appreciated! Thx!

LT
 
K

Keith Wilby

eltokaji said:
Hi,

we have to transfer datasheets into another program, but just turned
out that several thousands of lines has / sign in the end of the lines
(my mistake). In order to be transferred correctly, this has to be
removed. How con I solve this problem?
So I would need a VBA script to remove the last character only if this
a / sign .
Any help is appreciated! Thx!

LT
Click to expand...

Use Find and Replace to replace "/" with nothing.

Keith.
www.keithwilby.com
 
E

eltokaji

Replace is not an option because in every line there are almost 50 /
sign ...
I only need to remove the last / in every line.

LT
 
R

Rick Brandt

eltokaji said:
Hi,

we have to transfer datasheets into another program, but just turned
out that several thousands of lines has / sign in the end of the lines
(my mistake). In order to be transferred correctly, this has to be
removed. How con I solve this problem?
So I would need a VBA script to remove the last character only if this
a / sign .
Any help is appreciated! Thx!

LT
Click to expand...

No VBA needed. Just an update query.

UPDATE TableName
SET FieldName = Left(FieldName, Len(FieldName)-1)
WHERE Right(FieldName,1) = "/"
 
D

Douglas J. Steele

To change it permanently, try something like:

UPDATE MyTable
SET MyField = Left([MyField], Len([MyField]) - 1)
WHERE MyField LIKE "*/"

If you strictly want a read-only query that you can use to export, use:

SELECT Field1, Field2, MyField
FROM MyTable
WHERE MyField NOT LIKE "*/"
UNION
SELECT Field1, Field2, Left([MyField], Len([MyField]) - 1)
FROM MyTable
WHERE MyField LIKE "*/"
 
E

eltokaji

Thx Rick and Douglas,

I like your solutions!

I am quite new to Access. How can I do that update query, that u were
suggesting.
I did simple update queries myself, but those ones didn't contain
lines like that. So if you don't mind please tell where should I put
those lines.
Thx,

LT
 
D

Douglas J. Steele

To do it through the graphical interface:

1. Create a new query
2. Select the table and click on Add
3. Drag the field into the grid.
4. Put LIKE "*/" as the criteria.
5. Change the query to an Update query (under the Query menu)
6. Put Left([NameOfField], Len([NameOfField])-1) in the "Update To" row
under the field.

Alternatively, switch to the SQL view (View | SQL View from the menu) and
just type the SQL we gave you there.
 
E

eltokaji

Hey,

There seems to be a problem in Design view, it always gives syntax
errors. But in SQL view it was perfect! Thx!

LT
 
E

eltokaji

Hey again,


I was too fast saying that it works.
It recognizes perfectly the lines to be updated, but
it constantly gives compile errors and doesn't update any of my
data ....

What to do now?


LT
 
J

John W. Vinson

I was too fast saying that it works.
It recognizes perfectly the lines to be updated, but
it constantly gives compile errors and doesn't update any of my
data ....

What to do now?
Click to expand...

Post the SQL view of your table, and please also describe the
datatypes of the relevant fields.

John W. Vinson [MVP]
 
E

eltokaji

So Field2 in question:

is Memo type

Format empty
Caption empty
Default value empty
Validation Rule empty
Validation Text empty
Required No
Allow zero length Yes
Indexed No
Unicode compression Yes
IME code No Control
IME Sentence Mode None
Smart Tags empty


Thx for the effort!

LT
 
J

John W. Vinson

So Field2 in question:
Click to expand...

Memo was sufficient.

You are not getting errors in the field definition. You are getting errors in
the Query, right?

If you would like help fixing the errors in your Query, please - as requested
- open the Query in SQL view and post the SQL text here.

John W. Vinson [MVP]
 
T

The Harbour School

gfdrty


Memo was sufficient.

You are not getting errors in the field definition. You are getting errors in
the Query, right?

If you would like help fixing the errors in your Query, please - as requested
- open the Query in SQL view and post the SQL text here.

John W. Vinson [MVP]
Click to expand...
 
Top