Delete Spaces in Table field 1

[EddyLLC]

I have a table field with a field size of 10. It contains 5 letters and five trailing spaces. I am trying to load more data into the field but am getting an error telling the field size is to small so I am trying to delete the trailing spaces then add the new data. I am using the following code to delete the spaces but they are not deleted. Any suggestions are greatly appreciated.

Set rst = db.OpenRecordset("flkpTable", dbOpenDynaset)
rst("Field1") = RTrim(rst("Field1"))

 

[jadams0173]

Are you sure they are spaces? Try using just TRIM (although RTrim should work). Are you tried to rename the field or just take the spaces out of the data?

A brief example my help others understand what you are after.

I tried to have patience but it took to long! -DW

 

[AlexCuse]

you also want to be running an SQL update on your recordset. Like this:

dim strSQL as string
set strSQL = "UPDATE rst " & _
           "SET rst.FIELD1 = RTrim(rst.FIELD1)"

DoCmd.RunSQL strSQL

The wya you are trying to set it will not work, to the best of my knowledge.

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[EddyLLC]

Thanks for the replys. The field is a text field with a field size of 10. The field contains the data "ABCDE". I am trying to add into it "FGH". I am using the following code;

Set rst = db.OpenRecordset("flkpTable", dbOpenDynaset)
rst.Edit
rst("Field1") = rst("Field1") & "FGH"
rst.Update

When this code is run I receive an error telling me the field is to small to add the data. I use the Immediate Window to find the value of rst("Field1") it shows "ABCDE " (5 letters and 5 spaces) as the value.

I need to add the additional data to the field but I am sure missing something.
Thanks

 

[jadams0173]

Try this as a test

Function TEST()
Dim RST As DAO.Recordset
Dim DB  As DAO.Database


Set DB = CurrentDb

Set RST = DB.OpenRecordset("flkpTable", dbOpenDynaset)
RST.Edit
RST("Field1") = Trim(RST("Field1")) & "FGH"
RST.Update

Set DB = Nothing
Set RST = Nothing

End Function

I made a table and added 5 spaces to the end of my data. Using the code above it removed the spaces and added the "FGH" to the existing data.

I tried to have patience but it took to long! -DW

 

[jadams0173]

If all else fails try this. It will print the ASCII values in the imediate window.

Function TEST()
Dim RST As DAO.Recordset
Dim DB  As DAO.Database
Dim x  As Integer
Dim testthis As String


Set DB = CurrentDb

Set RST = DB.OpenRecordset("flkpTable", dbOpenDynaset)

For x = 1 To Len(RST("field1"))
    testthis = Mid(RST("field1"), x, 1)
    Debug.Print Asc(testthis)
Next x


RST.Close
Set DB = Nothing
Set RST = Nothing

End Function

You should see 32 (ascii for space) as the last digits in the immediate window. Something like this. I put the <space> in manually for clarification. You will only get the numbers in the immediate window.

 65 <A>
 66 <B>
 67 <C>
 68 <D>
 69 <E>
 32 <space>
 32 <space>
 32 <space>

I tried to have patience but it took to long! -DW

 

[EddyLLC]

It worked great! Thanks so much for you time and effort. I was pulling my hair out. Thanks for sticking with me.

 

[jadams0173]

Glad I could help!

I tried to have patience but it took to long! -DW