field values

  • Thread starter Thread starter LJG
  • Start date Start date
L

LJG

I have had this code passed to be to split data out of a table but am
finding the 'ref ' is not bringing the leading 0's into the table

i.e. original table:

000001 blu blk

what I want is:

000001 blu
000001 blk

What I get from this code is

1 blu
1 blk

code example below:

Sub PopulateNewTable()

Dim dbCurr As DAO.Database
Dim rst2t_new1 As DAO.Recordset
Dim lngLoop As Long
Dim lngRef As Long
Dim strColours As String
Dim strSQL As String
Dim varColours As Variant

strSQL = "SELECT ref, colours FROM t2t_new1"
Set dbCurr = CurrentDb()
Set rsOldData = dbCurr.OpenRecordset(strSQL)
Do While rsOldData.EOF = False
lngRef = rsOldData!ref
strColours = rsOldData!Colours
varColours = Split(strColours, " ")
If IsNull(varColours) = False Then
For lngLoop = LBound(varColours) To UBound(varColours)
strSQL = "INSERT INTO t2t_new2 (ref, colours) " & _
"VALUES (" & lngRef & ", '" & varColours(lngLoop) & "')"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
End If
rsOldData.MoveNext
Loop

rsOldData.Close
Set rsOldData = Nothing
Set dbCurr = Nothing

End Sub
 
I have had this code passed to be to split data out of a table but am
finding the 'ref ' is not bringing the leading 0's into the table

i.e. original table:

000001 blu blk

what I want is:

000001 blu
000001 blk
Click to expand...

Instead of using a numeric variable lngRef, use a String variable
txtRef. A Long Integer *is an integer* - and the integer 1 can be
represented as 1, 000001, or 000000000000000000000000000000000001
without having the least effect on its value. They're all the same
number.

John W. Vinson[MVP]
 
Hi and thanks for that, However, using the string still gives me the same
problem?

The field I am updating from is a text, field, 6 char leng , to text, 6,

anything else I can try?

TIA
Les
 
Back
Top