How do I sort in EBCDIC sequence in Access

  • Thread starter Thread starter Pete Chicago
  • Start date Start date
P

Pete Chicago

As opposed to ASCII, I want to sort on a column into EBCDIC sequence.
Sort Sequnecs EBCDIC: Lower Case, Upper Case, Numbers
Sort Sequence ASCII: Numbers, Upper Case, Lower Case
For example: Region Code column entries: 1AB1, 1AA2, 1A23
EBCDIC Result: 1AA2, 1AB1, 1A23
ASCII Result: 1A23, 1AA2, 1AB1

I want the Alphas before the numerics.
 
I can think of a very convoluted way.
Parse field into two fields per character within the field. First field of
the pair use IIF and ASC function to assign 1 or 0 based on whether it is an
alpha or numerial for first sort. Then the character for second sort.
Continuing for all characters within field for the maximum number of sort
pairs.
 
On Tue, 19 Feb 2008 13:52:01 -0800, Pete Chicago <Pete
As opposed to ASCII, I want to sort on a column into EBCDIC sequence.
Sort Sequnecs EBCDIC: Lower Case, Upper Case, Numbers
Sort Sequence ASCII: Numbers, Upper Case, Lower Case
For example: Region Code column entries: 1AB1, 1AA2, 1A23
EBCDIC Result: 1AA2, 1AB1, 1A23
ASCII Result: 1A23, 1AA2, 1AB1

I want the Alphas before the numerics.
Click to expand...

A quick Google search finds a whole raft of hits: ASCII EBCDIC Translator -
1 to 10 of about 165,000 hits. One of the early ones is

http://members.aol.com/getmydata/pskb/EBCDIC-ASCII-Convert.htm

which has code which could be adapted. You might want to have two fields, your
actual ASCII data and a sortkey field consisting of the EBCDIC translation.
 
Thanks Karl.
I had something very similar in mind. You affirmed my initial thoughts.
I was hoping for some sort of obscure setting that would do the trick, or a
CAST statement with an EBCDIC option, like in other DBs.
Thanks for the feedback.
 
nJohn, thanks for the post.
I'll take a look at this.
Although, I'm don't think I have an 'Advanced Scripting License from
Pinnacle Software '.
How would one use the logic in this script outside of Pinnacle?
 
nJohn, thanks for the post.
I'll take a look at this.
Although, I'm don't think I have an 'Advanced Scripting License from
Pinnacle Software '.
How would one use the logic in this script outside of Pinnacle?
Click to expand...

Look at a few of the other Google hits; I didn't spend long looking. I'm sure
there is a VB or VBA solution out there.
 
Back
Top