Results 1 to 6 of 6
Thread: Decimal to Hex (A97)

20030425, 19:26 #1
 Join Date
 Sep 2001
 Location
 Stuck at work..., Missouri, USA
 Posts
 248
 Thanks
 0
 Thanked 0 Times in 0 Posts
Decimal to Hex (A97)
Let's say I have the number 21103313536 and I want to convert it to hex.
Using Hex(21103313536) results in an overflow error.
What is the easiest way to do this?
Surely you can count this high in hex...<font face="Comic Sans MS">Morgan Erickson</font face=comic>
morgan.erickson@sprint.com
<img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

20030425, 19:47 #2
 Join Date
 Sep 2001
 Location
 Stuck at work..., Missouri, USA
 Posts
 248
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Decimal to Hex (A97)
It will be stored as a string.
<font face="Comic Sans MS">Morgan Erickson</font face=comic>
morgan.erickson@sprint.com
<img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

20030425, 19:47 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Decimal to Hex (A97)
Your number 21103313536 is larger than the largest long integer. How are you going to input this number? As a string?

20030425, 20:03 #4
 Join Date
 Aug 2001
 Location
 Evergreen, CO, USA
 Posts
 6,648
 Thanks
 3
 Thanked 65 Times in 64 Posts
Re: Decimal to Hex (A97)
What type of field are you trying to store it in? If the base type of number of you are dealing with is Long Integer then your max is
2 to 31st, which is a bit more than 2,000,000,000. Your number is 21,103,313,536 which would cause an overflow.
I don't have Access97 available at the moment, but the XP helps says:<pre><hr></pre>
Hex Function
Returns a String representing the hexadecimal value of a number.
Syntax
Hex(number)
The required number argument is any valid numeric expression or string expression.
Remarks
If number is not already a whole number, it is rounded to the nearest whole number
before being evaluated.
If number is Hex returns
Null Null
Empty Zero (0)
Any other number Up to eight hexadecimal characters
You can represent hexadecimal numbers directly by preceding numbers in the proper
range with &H. For example, &H10 represents decimal 16 in hexadecimal notation.
<hr>
In order to work with a number that big you will need to do some creative math I'm afraid, and then assign it to a string or variant type.Wendell

20030426, 14:26 #5
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: Decimal to Hex (A97)
As noted you could write your own function to convert decimal numbers larger than a Long Integer (2,147,483,647) to Hex, or, if lazy, "borrow" the Excel ATP Dec2Hex function, which can take a Double as its numerical argument, unlike the VB Hex function, which accepts only a Long (thus the overflow error you experienced). To use this function in Access, you'd have to set a reference to the Microsoft Office Web Components Function Library (MSOWCF.DLL) (which may or may not be installed on your system if still using Office 97). (You'd have to decide whether you want to incur the extra overhead  there's "only" 3 class modules in this library.... also note, you cannot directly set a reference to an Excel addin file (.XLA) in Access.) The OCATP Class provides many of the functions found in the Excel Analysis Toolpak (ATP) addin. Example of a wrapper function:
Public Function owcDec2Hex(ByVal dblNum As Double)
Dim obj As New MSOWCFLib.OCATP
owcDec2Hex = obj.Dec2Hex(dblNum)
Set obj = Nothing
End Function
Example of use (using the number in your example):
? owcDec2Hex(21103313536)
4E9DB0280
Unlike the VB Hex function, which maxes out at 2147483647, the ATP Dec2Hex function can handle any positive number up to 2^391 (549755813887):
? owcDec2Hex(2^391)
7FFFFFFFFF
? owcDec2Hex(2^39)
Error 536608732
So if any of the numbers you are processing are larger than 2^39, you will have to write your own function. NOTE: The Office Web Components used to be available for download for MS Office users, but I don't know if that's still the case.
HTH

20030428, 00:32 #6
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: Decimal to Hex (A97)
In further reply, if using the ATP function is not an option, here is example of a userdefined function you can use to convert large numbers (doubles) to Hex. This function will probably not win any efficiency awards, and is intended to be used with positive numbers only!! Sample code:
Public Function GetHex(ByVal dblNum As Double, _
ByVal intAndH As Integer) As String
' NOTE: Do not use for negative numbers!!
' intAndH 0 = no "&H", intAndH 1 = add "&H" to Hex string
Dim n As Integer
Dim i As Integer
Dim h As Integer
Dim intHex() As Integer
Dim strHex() As String
'Determine no of "places" for hex number:
Do
n = n + 1
Loop Until (16 ^ n) > dblNum
ReDim intHex(1 To n)
ReDim strHex(1 To n)
For i = n To 1 Step 1
h = h + 1
' Using Mod or Integer division results in Overflow error
intHex(h) = Fix(dblNum / (16 ^ (i  1)))
dblNum = dblNum  (intHex(h) * (16 ^ (i  1)))
strHex(h) = Int2Hex(intHex(h))
Next i
If intAndH = 0 Then
GetHex = Join(strHex, "")
Else
GetHex = "&H" & Join(strHex, "")
End If
Erase intHex
Erase strHex
End Function
Above function uses this function to convert numerical values from 0 to 15 to corresponding Hex digit:
Public Function Int2Hex(ByVal intNum As Integer) As String
Select Case intNum
Case 0 To 9
Int2Hex = CStr(intNum)
Case 10
Int2Hex = "A"
Case 11
Int2Hex = "B"
Case 12
Int2Hex = "C"
Case 13
Int2Hex = "D"
Case 14
Int2Hex = "E"
Case 15
Int2Hex = "F"
End Select
End Function
In testing, the GetHex function returned same hex values returned by VBA Hex function or the ATP Dec2Hex function, with exception noted for negative numbers, as the hex value for a negative number will vary depending on how many bits the hex value is representing. The GetHex function also was able to convert numbers larger than Dec2Hex function w/o error. Example:
? GetHex(2^42,0)
40000000000
? atpDec2Hex(2^42)
Error 536608732
Note that if using ACC 97, the Join function is probably not available, you'd have to modify function to concatenate the elements of the array "manually" using something like:
For i = 1 To n
GetHex = GetHex & strHex(i)
Next i
If intAndH = 1 Then
GetHex = "&H" & GetHex
End If
HTH