Trimming Spaces in a Field

[Baggie]

I want to eliminate spaces in my data. For example, bbb01 should be 01. I wrote this simple routine but it doesn't seem to work as the spaces are not eliminated. ???

Sub ElimSpaces()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT DENCOV FROM BenTable"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
    rs.Edit
    Trim (rs!DENCOV)
    rs.Update
    rs.MoveNext
Loop
rs.Close
db.Close
End Sub

Thank you!

 

[jdegeorge]

Is the "bbb" in your example 3 spaces? If so, TRIM([FIELD]) should work, and you can set that to the UPDATE TO value in an UPDATE query.

Jim "Get it right the first time, that's the main thing..."

 

[THoey]

The proble is that you are not setting your field equal to the trimmed value...

Sub ElimSpaces()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT DENCOV FROM BenTable"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
rs.Edit
'--- Wrong
Trim (rs!DENCOV)
'--- Right
rs!DENCOV = Trim (rs!DENCOV)
rs.Update
rs.MoveNext
Loop
rs.Close
db.Close
End Sub




Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Baggie]

Thanks to THoey, that works! It seems like it's the simple things that plague me the most. I'm rather new to VBA programming. I am now trying to work this routine into a larger one so I may have more questions. You mentioned that I may be in the wrong forum. Please advise as to which one would be better. Much obliged!

 

[THoey]

Baggie, gklad you got it working.

About mentioning you may be in the wrong forum. What you saw is my standard siganture (hence the general disclaimor) and is attached to all my posts. Just trying to help keep the site organized and to save time by having question writers give details, etc...
Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...