data conversion: string to number

[key]

Hi Friends,

I've got a field in a table like xxxx xxx and want to have xxxxxxx. Here is my code:

Do Until RS.EOF

RS.Edit
varString = RS![Number]
varString = Left(varString, 4) & Right(varString, 3)
varString = CLng(varString)
RS![Number] = varString
RS.Update
RS.MoveNext

Loop

it works almost perfect, though for the number 1001 001 Access changes it just to 1001. Do you have an idea what I did wrong?

Thanx + regards,

Key

 

[Drevlin]

Val(varString) would probably be easier.

HTH

 

[key]

still the same problem:

access truncate the last 3 digits!!!

No idea why!

Key

 

[Drevlin]

What version of Access are you using?

But there must be something more involved then, because if you type:

?Val("5005 005")

into the immediate window you get:

5005005

Does it do that for you?

 

[antomack]

A number cannot have leading zeros, by using CLng you are effectively removing the leading zeros. Since the Number field is actually text you are as well off removing the CLng line and running without it. With this "0001 001" would become "0001001" and will be saved as text.

Do Until RS.EOF

RS.Edit
varString = RS![Number]
varString = Left(varString, 4) & Right(varString, 3)
RS![Number] = varString
RS.Update
RS.MoveNext

Loop