Newbee needs help.

[meadt]

Hi Guys,

I am very new to VB programming (started last week) and have minimal previous experience with other languages.

I have thrown myself straight into the deep-end and I am trying to create a database with a large amount of procedures. A quick summary would be an engineering database that imports records from a .txt file, then the user can manipulate the records in numourous ways to gain relevent data.

At the moment I'm working on a particularly tricky porcedure (for me at least!) that needs to extract a 4 digit hex code from a string in one field for every record (example: HGUS=3C4C (01,11,01,11)) and then place these in a new field, maybe on a new table. The hex then needs to be converted into binary, split up again into several more fields, then compared to a look-up table as each bit of binary represents something.

At the moment the specific area that I need help with is extracting the hex and putting it into a new field/table. I've found the Mid command, but can't get it to work. How do I tell it which table and which field to work on? and how do i tell it to save the data to a new field?

As I said I'm a complete beginner, so any advice, however small would be very welcome.

Thanks in advance,

 

[DCrake]

First of all have you mastered the art of converting Hex to Binary yet? If so what approach have you taken.

This will help in choosing the best method for you main issue.

David

 

[meadt]

Ive found some code online, whether it works or not im not sure, i was going to try and get over this hurdle first before I worried myself with the conversion

Hex to binary:

Function h2b(hstr)
'convert hex string to binary string
cnvarr = Array("0000", "0001", "0010", "0011", _
"0100", "0101", "0110", "0111", "1000", _
"1001", "1010", "1011", "1100", "1101", _
"1110", "1111")
bstr = ""
For i = 1 To Len(hstr)
hdgt = Mid(hstr, i, 1)
cix = CInt("&H" & hdgt)
bstr = bstr & cnvarr(cix)
Next
h2b = bstr
End Function

 

[DCrake]

The text file you have imported into Access does the string get imported into different fields in your table? Or does it all go into one field. I know these questions are basic but it all helps in the end.

David

 

[meadt]

The "HGUS=3C4C (01,11,01,11)" is imported into one field. Frustratingly the format of the txt. file cannot be changed as its the output of another program.

 

[DCrake]

Next step:

If you create a query and bring in the table then in the first column include the function

AliasName:h3b([YourTextField])

Then view in datasheet mode, does it give you want you want?

David

 

[meadt]

No it doesn't work for me, although its probably something i've done, not the code thats the problem. Can't it be done with a Mid command? The only reason is that this process of selecting the hex word and converting it needs to be a completely automated process that takes place when the .txt file is imported, so there is no user input.

I've changed the hex to binary converter in the mean time, I got this working for me earlier on today:

Public Sub test()
Dim strHex As String
Dim BinNum As String
Dim lHexNum As Long
Dim HexNum As Variant
Dim i As Integer

HexNum = "002A"

For i = 1 To Len(HexNum)
If ((Asc(Mid(HexNum, i, 1)) < 48) Or _
(Asc(Mid(HexNum, i, 1)) > 57 And _
Asc(UCase(Mid(HexNum, i, 1))) < 65) Or _
(Asc(UCase(Mid(HexNum, i, 1))) > 70)) Then
BinNum = ""
Err.Raise 1016, "HexToBin", "Invalid Input"
End If
Next i

i = 0
lHexNum = Val("&h" & HexNum)
Do
If lHexNum And 2 ^ i Then
BinNum = "1" & BinNum
Else
BinNum = "0" & BinNum
End If
i = i + 1
Loop Until 2 ^ i > lHexNum
HexToBin = BinNum
MsgBox BinNum
End Sub

Thanks for your help,

 

[DCrake]

I just tried this in the immediate window

?h2b("3C4C")

it returned this
0011110001001100

is this correct or not

If you are passing the whole stirng

"HGUS=3C4C (01,11,01,11)"

Then h2b need to parse out the hex

hstr = Mid(hstr,6,4)

David

 

[meadt]

The value is correct, thats exactly what I would require as my output. However I've just run ?h2b("3C4C") in my immediate window and it still doesn't work. Would it be down to different versions?

Anyway, besides the HextoBinary issue, how would I run the mid command you gave me on a whole field in a table?

Apologies for the persistant questioning,

 

[DCrake]

Function h2b(hstr) As String

'convert hex string to binary string

hstr = Mid(hstr,6,4)

cnvarr = Array("0000", "0001", "0010", "0011", _
"0100", "0101", "0110", "0111", "1000", _
"1001", "1010", "1011", "1100", "1101", _
"1110", "1111")
bstr = ""
For i = 1 To Len(hstr)
hdgt = Mid(hstr, i, 1)
cix = CInt("&H" & hdgt)
bstr = bstr & cnvarr(cix)
Next
h2b = bstr
End Function

Then in a query use

Hex2Bin:h2b([YourTextFieldHere])

David

 

[meadt]

Got this to work eventually, thanks for your help, its exactly what i need