convert hex to string

[sxxgupta]

How do I convert Hext code output from a MySQL db to a string output in an Access db. Currently, the file is output in Excel format that I import into my access db. I have very limited knowledge about vba but am knowledgeable in creating queries, reports etc. I need a public function that I can call in my query to perform this conversion.

I got this link for this site
http://bytes.com/topic/visual-basic-...ert-hex-string

But I keep getting vba errors that are syntax related and I have no clue.....

[ADezii]

Right before bedtime I created a little Function for you that will convert a Hexadecimal String to a 'Plain Olde ASCII' String. Here goes:

1. Function Definition:
   

   Public Function fConvertHexToString(strHexString As String) As String
   Dim intLenOfString As Integer
   Dim intCounter As Integer
   Dim strBuild As String
    
   'Hex String must have a valid length, and it must be an even length
   If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
    
   intLenOfString = Len(strHexString)
    
   For intCounter = 1 To Len(strHexString)
     If intCounter Mod 2 <> 0 Then     'need Hex pairs
       'Retrieve the Value of the Hex Pair, then Convert to a Character,
       'then Append to a Base String
       strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
     End If
   Next

2. Sample Call:
   

   Debug.Print fConvertHexToString("7378786775707461204F4B3F")

3. OUTPUT from Function Call:
   

   sxxgupta OK?

4. Any questions, feel free to ask. If you need help implementing it in a Query, just let us know.
5. Lights Out! ADezii has left the building!

[sxxgupta]

Hi ADezii:

I called this function in my query. The query runs and opens up in access but the field that I use to call this function does not show me any strings...?

[ADezii]

@sxxgupta
Kindly post the SQL Statement.

[sxxgupta]

SELECT ActionList_1.arg1, Converthex([arg1]) AS arg11
FROM ActionList_1
WITH OWNERACCESS OPTION;

I have called your public function "Converthex" instead of fConvertHexToString. Otherwise everthing else is the same.

Public Function Converthex(strHexString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strBuild As String
 
'Hex String must have a valid length, and it must be an even length
If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
 
intLenOfString = Len(strHexString)
 
For intCounter = 1 To Len(strHexString)
  If intCounter Mod 2 <> 0 Then     'need Hex pairs
    'Retrieve the Value of the Hex Pair, then Convert to a Character,
    'then Append to a Base String
    strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
  End If
Next
 
End Function

[NeoPa]

@sxxgupta
How about posting some of the results from this query. Just ten reccords say.

PS. Nice one ADezii. I didn't know Val() could handle "&H".

[ADezii]

@NeoPa
Thanks NeoPa, it's the Olde Val() Trick! (LOL)

[ADezii]

@sxxgupta

1. The Field used to 'Call' this Function is a Calculated Field, and in the Query grid should look like:
   

   arg11:Converthex([arg1])

2. Are you sure that Converthex() is a 'Public' Function and 'NOT' a Public Sub-Routine?
3. As requested by NeoPa, kindly post a Result Set.

[NeoPa]

@ADezii
No. From the code posted it may be a function, but it never assigns a value.

"Why?" one asks oneself. Looking at post #2 it becomes evident. You left off the code after line #17. The line, I suspect, where you assigned the value in your original code :D

I suspect the missing lines would have been something like :

fConvertHexToString = strBuild
End Function

[ADezii]

@NeoPa
My apologies to you and the OP. You are, of course, 100% correct.

[NeoPa]

@ADezii
Apologies?? You gave me a giggle. What's to be sorry for :D

I'm sure that will be all that's required for the OP to get his code working. ... most of which was supplied by you originally I need hardly add.

[sxxgupta]

Thank you everyone for their help.

[subedimite]

For the function Converthex()

I can see that if I pass the parameter say 40 hex ( as number, For this I change the function argument type to avariant). It will convert into the Ascii value @.
Is there any function that will convert 40 Hex ( number) into "40" text?

Cheers!

[ADezii]

@subedimite
Debug.Print Hex$(Val(&H40)) will return the String 40