Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Trim Procedure
- Thread starter eavan
- Start date
What do you mean by trim?
Delete rows? Chop off the rightmost characters of the value in each field? Compact the database?
I think probably you mean the second one.
You should just be able to issue SQL to the Access database along these lines
Update MyTable set MyColumn = left(MyColumn, 10);
You could either store this in the access database as a query, and execute the query from your .net app, or just pass the sql to Access directly from your .net app
Hope this helps
Mark
Mark![[openup]](/data/assets/smilies/openup.gif "[openup] [openup]")
Delete rows? Chop off the rightmost characters of the value in each field? Compact the database?
I think probably you mean the second one.
You should just be able to issue SQL to the Access database along these lines
Update MyTable set MyColumn = left(MyColumn, 10);
You could either store this in the access database as a query, and execute the query from your .net app, or just pass the sql to Access directly from your .net app
Hope this helps
Mark
Mark
- Thread starter
- #3
thanks for getting back to me,
when i meant trim i meant the removing of white space at the beginning of field columns (bad entries) and the removing of white space between strings in field columns.Is there any chance that there is a module that could be used to do this for all the columns of an entire table.Cheers
when i meant trim i meant the removing of white space at the beginning of field columns (bad entries) and the removing of white space between strings in field columns.Is there any chance that there is a module that could be used to do this for all the columns of an entire table.Cheers
For the left trim thing, there is the ltrim function.
To do it for all columns of a table, you would just use a SQL command like this
Update MyTable set MyColumn = ltrim(MyColumn), MyOtherColumn = ltrim(MyOtherColumn) etc
You'd have to do it this way - there is nothing inherently there to do this. For example, what would happen to columns which are not Text Data types eg dates, numbers
As for getting rid of white space within a string, it depends. You said at the top of your post that you want this to be re-runnable in MSAccess. I don't know if you mean you want to save it as a query which can be run from the MS Access application. If so, you could create a VBA function which does the removal of white space and the trim and issue SQL to call this function similar to what I've done above with the ltrim function.
Alternatively, I don't know if you mean that you are connecting to the db from .net. In this case, I don't think calling a VBA function like this will work. You'd need to use ADO.net to loop thru all the records and update them. You might use system.text.regularexpressions to remove the white space.
Mark
To do it for all columns of a table, you would just use a SQL command like this
Update MyTable set MyColumn = ltrim(MyColumn), MyOtherColumn = ltrim(MyOtherColumn) etc
You'd have to do it this way - there is nothing inherently there to do this. For example, what would happen to columns which are not Text Data types eg dates, numbers
As for getting rid of white space within a string, it depends. You said at the top of your post that you want this to be re-runnable in MSAccess. I don't know if you mean you want to save it as a query which can be run from the MS Access application. If so, you could create a VBA function which does the removal of white space and the trim and issue SQL to call this function similar to what I've done above with the ltrim function.
Alternatively, I don't know if you mean that you are connecting to the db from .net. In this case, I don't think calling a VBA function like this will work. You'd need to use ADO.net to loop thru all the records and update them. You might use system.text.regularexpressions to remove the white space.
Mark
- Status