Text to Number Conversion

[infomania]

I have a text file I am parsing into an Access table. One of the fields has values like 0000029688 and should be resolved as a number: 29688.

The number of zeros is variable but the string length is always 10. Is there a simple (or complex) function I can use?

Thanks

 

[PHV]

Take a look at the Val function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infomania]

I get a #Name? returned when I use =Val([fieldname]). Any other suggestions?

 

[PHV]

And this ?
=CLng([fieldname])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infomania]

No luck
=CLng([fieldname]) returns #name?
=Val("[fieldname]") returns 0
=CLng("[fieldname]") returns #error

 

[PHV]

And doesnt =[fieldname] returns #name?
If yes, you may consider to use the expression builder to discover the correct syntax for referencing your control, and then play with the Val function as I mentionned in my first post.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infomania]

No. just fieldname as the control source returns the text (with zeros). =([fieldname]) as the control source returns #Error. I'll try some more.

 

[maxhugen]

Hi, you mention that you're parsing the file, so I'm assuming that you're using code.

Try creating this function:

Public Function ChangeToNumber()
Dim s$, n&
s = "0000012345"
n = CLng(s)
ChangeToNumber = n
End Function

In the immediate window type ?ChangeToNumber and you'll see a number returned.

Perhaps this simplistic example will help you resolve the issue you're having?

HTH

Max Hugen

http://www.AlphaFinancial.com.au

Sydney Australia