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

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
Asked:
acarpen271
  • 3
  • 2
  • 2
  • +2
1 Solution
 
JimFiveCommented:
What is the data type of the field CHECK_ACH_NO?
--
JimFive
0
 
Rey Obrero (Capricorn1)Commented:
the data type for that field  CHECK_ACH_NO should be Text
0
 
JimFiveCommented:
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, ".")
 
'Add Enough zeros
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
acarpen271,
set the field type to Text, use the same query as before

select CStr([CHECK_ACH_NO]) as CheckNo
from TableName


0
 
GRayLCommented:
I can get you to 28

CStr(CDec(Check_ACH_No))
0
 
GRayLCommented:
In the Immediate pane, I typed:

? Cstr(cdec(1.2345678E28))
12345678000000000000000000000

you could always do

? Cstr(cdec(1.2345678E28)) & "00"
1234567800000000000000000000000
0
 
Gustav BrockCIOCommented:
Format is the tool to choose:

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

returns as requested:
458712000000000000000000000000

/gustav
0
 
acarpen271Author 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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
GRayLCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now