Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Hexadecimal To 30 Digit Text String Problem

Posted on 2007-11-30
10
Medium Priority
?
355 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
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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

927 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