converting text values to number & decimal values..

[mls via AccessMonster.com]

I have any array which read a csv file and I am using split and populate
these fields into my table..
temp= 5245,test1,23.45
temp1=3456,test1,23.45

Now I want to convert my text fields to number and decimels respectively.
I tried the following functions but this works fine if they are number and
fails it was text value.

TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number
CT_num = CDec(ct_val)

 

[Clifford Bass via AccessMonster.com]

Hi,

Test to see if they are numeric first.

If IsNumeric(sampleid) Then
TESTID = CInt(sampleid)
Else
TESTID = 0
End If

Note that this will not tell you if it is an integer in the valid range
of values for integers. So you may get an error anyway. Use some form of
error processing to deal with that issue. Or, convert all values to doubles
first, and then check to make sure they are in the allowed range for integers
before converting to integers.

Clifford Bass

 

[John W. Vinson]

I have any array which read a csv file and I am using split and populate
these fields into my table..
temp= 5245,test1,23.45
temp1=3456,test1,23.45

Now I want to convert my text fields to number and decimels respectively.
I tried the following functions but this works fine if they are number and
fails it was text value.

TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number
CT_num = CDec(ct_val)

Note that Split() will default to a zero based array, so myarray(1) will
contain the text string "test1"; myarray(0) will be 5245. You might also want
to use CLng() rather than CInt() - the default number datatype in tables is
Long Integer (32 bits, range to two billion odd) rather than Short Integer (16
bits, limit 65535).

 

[mls via AccessMonster.com]

Thanks John,

CLng works perfect. But my ct_num is giving me EEROR Message when I use..
ct_num=CDec(myarray(3))
ct_num =CInt(myarray(3)) gives me round number..
What function should I use to convert my text value to DECIMAL number.

I have any array which read a csv file and I am using split and populate
these fields into my table..

[quoted text clipped - 7 lines]

TESTID = CInt(sampleid) 'CInt(myarray(1))Convert to number
CT_num = CDec(ct_val)

Note that Split() will default to a zero based array, so myarray(1) will
contain the text string "test1"; myarray(0) will be 5245. You might also want
to use CLng() rather than CInt() - the default number datatype in tables is
Long Integer (32 bits, range to two billion odd) rather than Short Integer (16
bits, limit 65535).

 

[mls via AccessMonster.com]

Sorry ERROR is 3759 "Scaling of decimal value resulted in date truncation"

Thanks John,

CLng works perfect. But my ct_num is giving me EEROR Message when I use..
ct_num=CDec(myarray(3))
ct_num =CInt(myarray(3)) gives me round number..
What function should I use to convert my text value to DECIMAL number.
[quoted text clipped - 7 lines]

Long Integer (32 bits, range to two billion odd) rather than Short Integer (16
bits, limit 65535).

 

[John W. Vinson]

CLng works perfect. But my ct_num is giving me EEROR Message when I use..
ct_num=CDec(myarray(3))
ct_num =CInt(myarray(3)) gives me round number..
What function should I use to convert my text value to DECIMAL number.

Ummm...

Press F1.

Look for Help on Decimal.

Hint: CInt converts to INTeger. CLng converts to LoNG. CDec converts to
DECimal.

 

[mls via AccessMonster.com]

I tried all the possible ways and if I use CInt or Clng it is roundinglike 19.
32 to 19
Any other thoughts?

 

[John W. Vinson]

I tried all the possible ways and if I use CInt or Clng it is roundinglike 19.
32 to 19

Exactly.

CInt converts a value TO AN INTEGER. An Integer is, by definition, a whole
number without decimal places.

CLng converts a value TO A LONG INTEGER. An Integer is, by definition, a whole
number.

Any other thoughts?

Yes, as posted previously: use CDec (rather than CInt or CLng) to convert the
value *TO A DECIMAL* datatype, which *does* include decimal places.

 

[mls via AccessMonster.com]

Some how I could not use CDec , it shows error: 3759
Anyway I uesd the following.. format to get 2 digit decimal.

CT_num = Format(myarray(3), "####.##")
Else
CT_num = 0
End If
works perfect.
Thanks John.