Text to number - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Text to number

Achaiki Diachiristiki

3 years ago

I have a small problem that I did not find a way to solve. The problem concerns the exact conversion of a text to a number, In fact I have a field in a text in a table that contains the text in 123457006320490323011271500 format with 27 digits. I want to transfer this value to another number this time field in exactly the same digits (123457006320490323011271500) as a number so that I can do mathematical operations. (I should divide this number by 97)
This number is an RF payment number.
I tried a few ways like command CLng or CDbl but the result was something like this: (1,2345700632049E+26) or (123457006320490000000000000)
I try to do this whole process with an update query to change all the values in the table.

Kevin Robertson @Reply

3 years ago

Try with a little VBA code:

    Dim rs As Recordset, myNumber As Variant, X As Long



    Set rs = CurrentDb.OpenRecordset("LargeNumberT")



    X = 0

    

    While Not rs.EOF

        myNumber = CDec(rs.Fields("MyNumberStr"))

        myNumber = myNumber / 97

        rs.Edit

        rs.Fields("MyNumber") = Int(myNumber)

        rs.Update

        X = X + 1

        rs.MoveNext

    Wend



    rs.Close

    Set rs = Nothing



    MsgBox X & " values updated!"

Using the value specified in your post the following was returned (which I verified as correct with Calculator):

1272752642479281680528572

Remember to change the Table name and Field names to what you have in your database.

	Kevin Robertson @Reply 3 years ago

	Achaiki Diachiristiki OP @Reply 3 years ago

	Achaiki Diachiristiki OP @Reply 3 years ago

	Achaiki Diachiristiki OP @Reply 3 years ago
Thanks for the effort you have made but unfortunately it does not work properly as you can see in the pictures

	Kevin Yip @Reply 3 years ago
Click on each of the boxes with all the "E+24" and the number should expand fully. Long numbers have a max value of 2,147,483,647 (only 10 digits), and your number has 25 digits, so that's why CLng() doesn't work. Double numbers have only 15 precision (15 max digits), so CDbl() doesn't work either. Decimal numbers have 28 max precision so that's why Kevin's usage of CDec() works.

	Alex Hedley @Reply 3 years ago
New in Access 2016'Large Number (BigInt) support Using the Large Number data type > -2^63 ('9,223,372,036,854,775,808) to 2^63 ' 1 (9,223,372,036,854,775,807)

	Alex Hedley @Reply 3 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View