Solved

Hexadecimal To 30 Digit Text String Problem

Posted on 2007-11-30
10
345 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now