Trailing spaces problem (1 Viewer)

[gblack]

Trailing spaces problem [SOLVED]

So,
I pull some data into a recordset (I call: rsSTART), then I create a table using VBA open it with another recordset (I call: rsBUILD_A)

All I am trying to do is use positional logic to fill the data from rsStart into rsBUILD_A.

The problem is, when I push the values from START into A... The CHAR fields created seem to keep their 255 character length, so I end up with 255 trailing spaces (minus the length of the values from rsSTART).

The crazy thing is, Trim and RTrim appear to do nothing to prevent the 255 length characters.

The code below is a section of my overall sub... just to give you an idea of what I am doing here...

strSQL = "SELECT CATEGORY_01, CHILD AS CATEGORY_02, DATA_STRUCTURE.LEVEL " & _
         "FROM DATA_STRUCTURE INNER JOIN BUILD_START ON DATA_STRUCTURE.PARENT = BUILD_START.CATEGORY_01 " & _
         "WHERE [LEVEL]=2;"
rsSTART.Open (strSQL)
rsSTART.MoveFirst

db.Execute "CREATE TABLE BUILD_A " & _
       "(CATEGORY_01 CHAR, CATEGORY_02 CHAR, LEVEL INTEGER);"

strSQL = "SELECT * FROM BUILD_A ;"
rsBUILD_A.Open (strSQL)

Do While Not rsSTART.EOF
 
    ' Fill BUILD_A
    rsBUILD_A.AddNew
    For i = 0 To 2
        rsBUILD_A.Fields(i) = rsSTART.Fields(i)
    Next i
    rsBUILD_A.Update

    rsSTART.MoveNext

Loop

How do I get rid of these trailing spaces, beforehand?

 

[pbaldy]

Try Text(255) instead of CHAR.

 

[gblack]

Yep that was the issue... that'll teach me for using Microsoft's example....

How do you mark this thing [SLOVED] like I see in other posts? It's probably right in front of my face, but I don't see it...

 

[pbaldy]

You can edit the thread and change the prefix.