How to convert String to Number Format

[evilbungle]

Hi,

I have a field in my database which contains numbers stored as string, I need to output these as a number.

The numbers are 13 digits in length (i.e 1900026354527) they are stored in the table as a Text field called MPAN I thought it would be a simple convert function so tried.

Clng([MPAN]) but this returns #Error, I believe the numbers are well within the range of a Long Integer so I really can't tell what is wrong.

Any help appreciated

[Steven Kogan]

Long actually only stores numbers from -2,147,483,648 to +2,147,483,647.

This works:

CDbl("1900026354527")

[Stewart Ross]

The example you give is outside of the range of a long integer, as you will see if you look at the help entry for the long data type (or look it up on MSDN):

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from
-2,147,483,648 to 2,147,483,647.

You can handle numbers of this size using the Currency type. Although it is not the most obvious of choices, the Currency type will handle values in the range -922,337,203,685 ,477.5808 to 922,337,203,685 ,477.5807. It is not a floating point type, but is instead a scaled integer type using 8 bytes to represent the value instead of the long integer's four bytes.

Like all integer-based types, calculations involving addition, subtraction, and multiplication can be represented exactly as long as the resultant value is within the value limitations listed above (i.e. they don't cause an overflow). There is no approximation of values, which is inherent in floating-point types such as double.

Although by default Access will format currency values with the local symbol for currency this does not affect how they are stored, only how they are displayed. You can change this by formatting the number as necessary when it comes to presentation.

-Stewart