Solved

Hexadecimal To 30 Digit Text String Problem

Posted on 2007-11-30
10
349 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
Industry Leaders: 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!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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