Solved

Hexadecimal To 30 Digit Text String Problem

Posted on 2007-11-30
10
351 Views
Last Modified: 2013-11-27
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
Comment
Question by:acarpen271
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 20385527
What is the data type of the field CHECK_ACH_NO?
--
JimFive
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20385560
the data type for that field  CHECK_ACH_NO should be Text
0
 
LVL 15

Expert Comment

by:JimFive
ID: 20385638
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

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

select CStr([CHECK_ACH_NO]) as CheckNo
from TableName


0
 
LVL 44

Expert Comment

by:GRayL
ID: 20385816
I can get you to 28

CStr(CDec(Check_ACH_No))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20385832
In the Immediate pane, I typed:

? Cstr(cdec(1.2345678E28))
12345678000000000000000000000

you could always do

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

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 20394203
Format is the tool to choose:

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

returns as requested:
458712000000000000000000000000

/gustav
0
 

Author Comment

by:acarpen271
ID: 20396930
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 20396980
You are welcome!

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20397033
Elegant gustav.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question