Locating and converting Hex values

[Gangrel]

Here's my problem. I have a table that is used to create a text file that is loaded onto an os390 mainframe, which is EBCIDAC. In my table, there are some characters that do not translate properly during the conversion from Unicode to EBCIDAC. What I need to so is search for these characters and convert them into something that EBCIDAC will recognize.

To do these searches, I need to locate items using HEX. As an example, some rows have spaces that show up in HEX as X'41', which is no good on the mainframe, so I need them converted into the 'regular' space character.

Is there a way to search using a query for hex values?

I currently clean up other characters (that are typeable) using update queries, and would like the same thing for this weird space issue.....


Any help or ideas would be greatly appreciated!

 

[lynchg]

Look in Access Help for the Hex function.

 

[Gangrel]

The only functions I have in Access 2000 here at work are to convert Hex into Decimal, or Hex into Octal, or into Binary.

Nothing shows up in my help menu but those.

 

[PHV]

Have a look at the Replace, Asc and Chr functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gangrel]

Ok, i found on Google (but not in the Access help menu) that there is supposedly a HEX function that returns the character represented. To test it out, I try:

SELECT * FROM TMSG 
WHERE MSG_TXT_FRC  Like '*HEX(41)*';

and get no results, even though that hex characer is in the database. I have tried others too(such as HEX(61) which is the letter 'a'), but no dice.

Can i not use the HEX function in an SQL? Or do i have the SQL formatted wrong and it's actually looking for the term "HEX(41)" in my table?

I'm using the REPLACE one in other SQLs with no problems...... I'm going to have to do some VB coding, aren't I? (i was hoping to be simple....)

 

[PHV]

Seems you didn't make sense with my previous post ...
What about this ?
WHERE MSG_TXT_FRC Like '*' & Chr(65) & '*'

Note: 65 = X'41' and Chr(65)=A (in ASCII), so I wonder, unless MSG_TXT_FRC is already converted to EBCIDAC (EBCDIC ?)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886