convert "Text" into "Number"

[ali]

I have a a table (due to many reasons!), my query of "price" is text,


Now,

I want to convert it from "Text" into "Number".

(Because later on i need to export it to other system)




Thanks a lot , dear expert!

 

[Keith Wilby]

I have a a table (due to many reasons!), my query of "price" is text,


Now,

I want to convert it from "Text" into "Number".

(Because later on i need to export it to other system)

CInt or CLng should do it.

Keith.

 

[Ofer Cohen]

Clng and Cint will round the number,
34.4

will turn to 34

if you don't want the number to round, use
CDbl([YourNumber]) or Val([YourNumber])

 

[John W. Vinson]

I have a a table (due to many reasons!), my query of "price" is text,


Now,

I want to convert it from "Text" into "Number".

(Because later on i need to export it to other system)

I'll reluctantly have to disagree with both Ofer and Keith here. If the price
is a currency value, you want to convert it to Currency: CCur([price]) will do
it, with some caveats.

For one, CCur will only work if the number in the text field is just a number.
Whitespace and currency symbols such as $ are ok, but if there is other text
in the field, or if there are NULL values, you'll get errors.

What is actually IN your Price field? Could you post some examples? And what
are some of the reasons that you are storing non-currency data in a Price
field?

 

[E-mail report using Lotus Notes rather t]

Hi Keith,

I can't seem to convert text a field into a number using query.


FILED_ID: Cint([TableName]![FieldName])

When I use the expression above, it just returned an empty text filed after
executing the query.

Please help...this conversion is driving be nuts.

Thank you.

 

[John W. Vinson]

Hi Keith,

I can't seem to convert text a field into a number using query.


FILED_ID: Cint([TableName]![FieldName])

When I use the expression above, it just returned an empty text filed after
executing the query.

Please help...this conversion is driving be nuts.

What's the context? What's the actual value of FieldName?

You should be using . rather than ! as a delimiter here, and (if there is only
one field of this name) you can omit the TableName altogether:

FILED_ID: CInt([TextFieldName])

will return an integer 3 if TextFieldName is a Text field in the query
containing the text string "3".

If the field contains "Three" instead... you've got a harder problem!

 

[E-mail report using Lotus Notes rather t]

Keith the below expression, there is a limit on how many rows it can convert
to long Integer. Is there another way to extent to great then the number
below?

test: CLNG([FieldName])

only convert text to numbers up until 900099999


thanks.

Clng and Cint will round the number,
34.4

will turn to 34

if you don't want the number to round, use
CDbl([YourNumber]) or Val([YourNumber])


--
Good Luck
BS"D

CInt or CLng should do it.

Keith.

 

[John W. Vinson]

Keith the below expression, there is a limit on how many rows it can convert
to long Integer. Is there another way to extent to great then the number
below?

There is no limit on the number of *rows*.

test: CLNG([FieldName])

only convert text to numbers up until 900099999

There is a limit on the size of the number. A Long Integer (the output of
CLng) is a 32 bit signed integer with a valid range of values from -2147483648
through 2147483647. 900099999 is well within this range:

?clng("900099999")
900099999
?clng("2147483647")
2147483647
?clng("2147483648")
<generates an overflow error>

Originally, you were using CInt(), which generates a small (16 bit) integer,
which is limited to the range -65536 to 65535.

Could you explain the nature of the data, the range of values you're using,
and just what you are trying to accomplish?