Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    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.

  2. #2
    3 Star Lounger
    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 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>
    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>
    </pre>

    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

  5. #5
    Bronze Lounger
    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 add-in file (.XLA) in Access.) The OCATP Class provides many of the functions found in the Excel Analysis Tool-pak (ATP) add-in. 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^39-1 (549755813887):

    ? owcDec2Hex(2^39-1)
    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

  6. #6
    Bronze Lounger
    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 user-defined 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •