• Status: Solved
• Priority: Medium
• Security: Public
• Views: 359

# Hexadecimal To 30 Digit Text String Problem

I have a field, CHECK_ACH_NO, with the following format:

4.58712E+29

I need it converted to this: 458712000000000000000000000000, or at least the first 10 digits.

I'm using this to convert: CStr([CHECK_ACH_NO]), however, it does not work at all. When I query the table, the output looks exactly the same as it did before the query, 4.58712E+29. Is there another method I can use to convert this to a text string or a number? I only have to have the first 10 digits of the 30 that are there, the last 20 are just zeros, but all 30 would be fine too.
Thanks (again).
0
acarpen271
• 3
• 2
• 2
• +2
1 Solution

Commented:
What is the data type of the field CHECK_ACH_NO?
--
JimFive
0

Commented:
the data type for that field  CHECK_ACH_NO should be Text
0

Commented:
Ok, CSTR doesn't work because it is already a string.
Create a module. in the module paste this attached function.
call it with expand("4.58712E+29")

``````Function expand(x As String)
Dim xp, e, dp As Integer

'Find the Exponent
e = InStr(1, x, "E")
'Find the Exponent Value
xp = CInt(Mid(x, e + 1))
' Get the Mantissa
x = Mid(x, 1, e - 1)
'Find the decimal point
dp = InStr(1, x, ".")

If xp > 0 Then
x = Mid(x, 1, e - 1) & String(xp - Len(x) + dp, "0")
'Get rid of the decimal point
x = Replace(x, ".", "")
Else
x = String(Abs(xp), "0") & Mid(x, 1, e - 1)
'Move the Decimal Point
x = Replace(x, ".", "")
Mid(x, 2, 1) = "."
End If

expand = x
End Function
``````
0

Commented:
acarpen271,
set the field type to Text, use the same query as before

select CStr([CHECK_ACH_NO]) as CheckNo
from TableName

0

Commented:
I can get you to 28

CStr(CDec(Check_ACH_No))
0

Commented:
In the Immediate pane, I typed:

? Cstr(cdec(1.2345678E28))
12345678000000000000000000000

you could always do

? Cstr(cdec(1.2345678E28)) & "00"
1234567800000000000000000000000
0

CIOCommented:
Format is the tool to choose:

strNumber = Format(4.58712E+29, String(30, "0"))

returns as requested:
458712000000000000000000000000

/gustav
0

Author Commented:
The data type is a number, since it's a linked table I cannot control the properties. I've tried CStr([CHECK_ACH_NO]) but it has no affect on the output format, and this gives me invalid syntax error: Cstr(cdec[CHECK_ACH_NO]). The last solution by cactus data worked correctly.. thanks gustav!
0

CIOCommented:
You are welcome!

/gustav
0

Commented:
Elegant gustav.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.