Solved

Hexadecimal To 30 Digit Text String Problem

Posted on 2007-11-30
10
347 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 49

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 49

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

823 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