Case Sensitivity in Primary Key fields (1 Viewer)

[Gareth50]

Dear all

Can you help! I have imported a Dbase5 file into access that uses the full range of ascii characters to make up the primary key field. eg. £%{A or ""$6

The problem is, they use a mix of upper and lower case letters and when imported Access sees upper and lower case as the same:

£$$}A is seen as the same as £$$}a

Does anyone know how to tell Access to treat these as different values - ie. make a field entry case sensitive. I'm sure it must be possible as you can set the Find function to be case sensitive.

It's driving me mad - HELP!!

 

[jimbrooking]

I think if it was MY database, I'd take the easy way out and convert all the key values into numbers:

NumKey = 256*(Asc(Mid(AlphKey,1,1))+256*(Asc(Mid(AlphKey,2,1))+256*(Asc(Mid(AlphKey,3,1))+Asc(Mid(AlphKey,4,1)))))+Asc(Mid(AlphKey,5,1))

(The parens may not be right - count'em.)

If the conversion was one-way, never to return to DBase5, you do this conversion once and forget it. If you have to go backward you need to use the Mod operator and Chr$ functions to recreate the original indices.

There're probably cleverer ways to do this, but that ought to work.

 

[Gareth50]

Hi Jim

Many thanks for the tip, I will try this and see how it goes. This is a live Link to a much larger Dbase database that is continually being updated so my solution has to read the Dbase table then update values in my access table each time the Access db is loaded. I'll let you know how it went.

Thanks again.

Gareth

I think if it was MY database, I'd take the easy way out and convert all the key values into numbers:

NumKey = 256*(Asc(Mid(AlphKey,1,1))+256*(Asc(Mid(AlphKey,2,1))+256*(Asc(Mid(AlphKey,3,1))+Asc(Mid(AlphKey,4,1)))))+Asc(Mid(AlphKey,5,1))

(The parens may not be right - count'em.)

If the conversion was one-way, never to return to DBase5, you do this conversion once and forget it. If you have to go backward you need to use the Mod operator and Chr$ functions to recreate the original indices.

There're probably cleverer ways to do this, but that ought to work.

 

[Gareth50]

The case continues

Dear Jim and everyone who likes a challenge

I'm afraid this didn't work. Of course if you evaluate each character and sum these numbers in many cases you arrive at the same number which means it's no good as a primary key.

Any further suggestions will be very welcome.

Thanks

Gareth

 

[jimbrooking]

Gareth,

If you are using a 256-character code set (e.g., ASCII), the encoding is unique. It's easy to see if you think of it in hexadecimal. With a 5-character key, e.g., AbCdE, the hex value of the equivalent numeric key would be something like

X'6192639465

As you can see, each character maps uniquely into a hex number between 00 and ff (decimal 0 to 255), and the character positions (in Hex) don't overlap adjacent character positions. So the mapping I suggested is 1-to-1 in both directions.

My original comment about parentheses was not to get you to delete them (and thus add all the characters together) but to be sure they were properly nested. Perhaps a clearer (but more computationally intense) statement would have been

NumKey = 256^4*Asc(Mid(AlfKey,1,1)) + 256^3*Asc(Mid(AlfKey,2,1)) _
+256^2*Asc(Mid(AlfKey,3,1)) +256*Asc(Mid(AlfKey,4,1))+Asc(Mid(AlfKey,5,1))

Jim

 

[Gareth50]

Dear Jim

You're a star and I'm a dipstick!

Your first method would have worked and so does your second. The problem was I had the field type set to Text which is what was producing multiple values. Once I reset the field to numeric it did indeed produce unique numbers.

I am very grateful.

Thanks

Gareth

 

[Gareth50]

Dear Jim (& Friends)

The Case continues insensitively!!

Thought you had solved the problem but unfortunately many of the graphic charcters return the same code - 166.

Have been on to the writers of the original db and they tell me the coding is called a 'crash code' and is produced by an application called FOX. They don't know how to covert it!

I have attached a small excel file that contains a sample of these codes to illustrate the problem.

Hopefully

Gareth

 

[jimbrooking]

Gareth,

Would it be possible to post at least a few (say 2-400) actual keys? I still believe if the keys from DBase are unique, and can be parsed apart in 8-bit chunks, you can use the conversion technique I suggested, or something similar. You might have to take the key field and convert it to binary, say, then take the binary number 8 bits at a time, convert the 8-bit chunck to decimal, etc. Or perhaps convert the field to binary, then convert the reulting binary number to decimal - more straightforward than the multiplication by 256.

The only problem I can see is if the import is doing some of its own conversion, and losing information in the keys as it brings then into Access.

Jim

 

[Gareth50]

Hi Jim

Attached is a small sample of the db table causing the problem.

See what you can do.

Gareth

 

[jimbrooking]

OK - the problem is, the DBase characters coming in are encoded in some character set wider than ASCII (i.e., more than 8 bits per character) - possibly Unicode, 'tho I have no experience with Unicode.

The numeric equivalent of a Unicode character can be obtained with the AscW (w-wide?) function.

In the attachment, look at qryJim, which builds a "NewIndex" by multiplying the 3rd character by 10,000 and adding the 4th character. I didn't use the 1st two characters because they all seem to be the same. Also, 10,000 may not be big enough, particularly if any of the the 3rd character's AscW values are over 10,000.

This ought to work unless you need to encode a 3rd long character, in which case you can not fit it in a Long-type numeric field. In that case you'd probably have to go to a 2-field key.

If the first 2 characters are all "short", you could use their max. values as a multiplier, instead of 10,000 or whatever the max. size of the low-order characters might be - that might fit the new key into a long-type field.

If you make assumptions that all the lower-order characters are less than 10,000 (as I did in the query) it would be a good idea to put in a diagnostic message in case one of them exceeds the assumed maximum size.

I guess this horse is dead, so I'm going to stop beating it. It's been interesting, and I have certainly learned from it. Hope it's been useful to you as well.

Jim