how to Trim Trailing spaces on SQL table

[AkAlan via AccessMonster.com]

I have a table in sql that was imported from MS Access. One of the feilds was
a memo feild and now is a varchar with a length of 2000. The problem I have
is that there seems to be a lot of trailing spaces which cause problems when
runing reports. I use the Can Grow property and some text boxes grow very
large but with only a few characters in the feild. I know about the Trim
function but don't know how to apply it to a table in sql. I'm thinking
somethind like this which I found in another post:

With TblTest
Do While Not .Eof
.fields("Question").value = trim("Question").value
.update
.movenext
loop
.close
end with



My question is how do I reference the table from code before I run this
snipet. Thanks

 

[Robert Morley]

While the way you suggest below should work, it would be very slow.

You can do the same thing from within SQL Server itself, which will be
significantly faster:

UPDATE TblTest SET Question = RTRIM(Question)


Rob

 

[AkAlan via AccessMonster.com]

Thanks for the fast reply. I stil don't know how to reference the table
before performing the UPDATE statement. I have only worked with recordsets so
far. Here is where I am with that:

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim count As Integer
Dim strSql As String
Dim intQuestion As Integer

strSql = "SELECT QCQuestion FROM QcQuestions "

Set cnn = CurrentProject.Connection
With rst
.Source = strSql
.ActiveConnection = cnn
.Open

End With

So how do I reference the table instead of a recordset? Thanks

While the way you suggest below should work, it would be very slow.

You can do the same thing from within SQL Server itself, which will be
significantly faster:

UPDATE TblTest SET Question = RTRIM(Question)

Rob

I have a table in sql that was imported from MS Access. One of the feilds was
a memo feild and now is a varchar with a length of 2000. The problem I have

[quoted text clipped - 15 lines]

My question is how do I reference the table from code before I run this
snipet. Thanks

 

[AkAlan via AccessMonster.com]

I figured it out and will post how I did it for any one else looking to do
dthe same.

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim strSql As String

strSql = "SELECT QCQuestion FROM QcQuestions"

Set cnn = CurrentProject.Connection
With rst
.Source = strSql
.ActiveConnection = cnn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
Do Until rst.EOF
rst.Fields("QCQuestion") = Trim(rst.Fields("QCQuestion"))
rst.MoveNext
Loop

Set rst = Nothing
Set cnn = Nothing

Thanks for the fast reply. I stil don't know how to reference the table
before performing the UPDATE statement. I have only worked with recordsets so
far. Here is where I am with that:

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim count As Integer
Dim strSql As String
Dim intQuestion As Integer

strSql = "SELECT QCQuestion FROM QcQuestions "

Set cnn = CurrentProject.Connection
With rst
.Source = strSql
.ActiveConnection = cnn
.Open

End With

So how do I reference the table instead of a recordset? Thanks

While the way you suggest below should work, it would be very slow.

[quoted text clipped - 10 lines]

 

[Tom van Stiphout]

On Wed, 14 May 2008 21:49:18 GMT, "AkAlan via AccessMonster.com"

While your solution works, it can also be INCREDIBLY MUCH SLOWER than
the suggestion that Robert made, especially on large data sets. The
point is that you are processing records one at a time, whereas the
UPDATE statement uses the set-based SQL language to process the rows
much more efficiently.
If you're using ADO, you can use the connection.Execute method.

-Tom.

I figured it out and will post how I did it for any one else looking to do
dthe same.

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim strSql As String

strSql = "SELECT QCQuestion FROM QcQuestions"

Set cnn = CurrentProject.Connection
With rst
.Source = strSql
.ActiveConnection = cnn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
Do Until rst.EOF
rst.Fields("QCQuestion") = Trim(rst.Fields("QCQuestion"))
rst.MoveNext
Loop

Set rst = Nothing
Set cnn = Nothing

Thanks for the fast reply. I stil don't know how to reference the table
before performing the UPDATE statement. I have only worked with recordsets so
far. Here is where I am with that:

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim count As Integer
Dim strSql As String
Dim intQuestion As Integer

strSql = "SELECT QCQuestion FROM QcQuestions "

Set cnn = CurrentProject.Connection
With rst
.Source = strSql
.ActiveConnection = cnn
.Open

End With

So how do I reference the table instead of a recordset? Thanks

While the way you suggest below should work, it would be very slow.

[quoted text clipped - 10 lines]

My question is how do I reference the table from code before I run this
snipet. Thanks