Alpha to numeric Data conversion

[Joe Troxtel]

This sound like is it should be easy... But I can't seam to figure out how to convert alpha characters entered into a field to a number the same field.

ie:

Enter into "Field 1" a single letter A-Z

"Field 1" should return a single number 1-26

A=1

B=2

etc...

Any Ideas?

Thanks in advance!

Joe T.

[RalphL]

I just tried this and it works. Field1 is a text feild which is set to auto enter by calculation allowing replacement of existing value. The calculation is Let ( X ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Case ( Position ( X ; Field1 ; 1 ; 1 ) ; Position ( X ; Field1 ; 1 ; 1 ) ; Field1) )

[Joe Troxtel]

YOU DA MAN RALPH! Just plugged it in and I am off to the races.

Thanks for the help!

Joe T.

[sbg2]

You also might try:

Let ( X ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Position(X;Left(Field1;1);1;1))

This way if a user enters more than one character the function will return the value of the first character.

A slight problem I see is what happens if a user accidently misses the "J" key and hits the "K" key instead? Are they going to realize, oops Field1 now equals 11, I meant for it to equal 10?

[Joe Troxtel]

Thanks for the insight "sdg2". It could be possible for double digit letters to be added into this field. If I want to make your calc add the two numbers together after conversion should I just put a Plus sign between two Let fuctions? Now that I think about it. It could be possible for the user to enter up to 4 digits "AAAA" if so the clac should return "4".

If a "J" is entered instead if a "K" that shouldn't mater because the user can enter whatever alpha character they wish A-Z

[sbg2]

Let ( X ="ABCDEFGHIJKLMNOPQRSTUVWXYZ";

// first character

Position(X;Left(Field1;1);1;1) +

// second

Position(X;Middle(Field1;2;1);1;1) +

// third

Position(X;Middle(Field1;3;1);1;1) +

// fourth

Position(X;Middle(Field1;4;1);1;1))

My point with a typo is that the user has no visual cue that they made a typo. In the above calculation ABCE returns 11 but a typo of ABC3 returns 6. In that particular case the user might be able to work out that they must have made a typo. In other cases, such as entering ADGK instead of ADHK, the user may not realize they made a typo.

Is there a reason you want to replace Field1 with the calculated value rather than using a seperate field to perform and display the calculation?

[Joe Troxtel]

THANKS "sbg2" !

Perfect! All the plants are aligned. And life is good... for now...

Thanks Again.

Joe T.

[Joe Troxtel]

Thanks again for your help on the Alpha to numeric conversion problem.

Now I have the opposite problem. I have ran across the need to find the greatest alpha character entered in the database. Is there a way to do some type of reverse calc to find the greatest alpha character?

if "A" = 1 and "A" is the greatest Alpha character entered I should be able to do a find for anything greater than "1" and add 1 to the number found. The result should be "2" = "B".

By the way... I am now using a separate field for the alpha to numeric calc.

Any insight will be greatly appreciated!

[sbg2]

Now I have the opposite problem. I have ran across the need to find the greatest alpha character entered in the database. Is there a way to do some type of reverse calc to find the greatest alpha character?

if "A" = 1 and "A" is the greatest Alpha character entered I should be able to do a find for anything greater than "1" and add 1 to the number found. The result should be "2" = "B".

I'm not sure I understand what you are asking for. If "A" is the greatest character entered in the Database then a find for anything greater than "A" would return no found records.

[Joe Troxtel]

What I am try to achieve is the next alpha character that comes after the greatest found alpha character. I will always be searching the alpha "field 1" containing a "A-Z" character using the previous alpha/numeric calc (thanks again) will return in "field 2" a number between "1-26"

First example:

Found "A" which = "1"

add "1" to the numeric result of my find which = "2"

I need a clac that will convert "2" to "B"

Second example:

Found "B" which = "2"

add "1" to the numeric result of my find which = "3"

clac. - convert "3" to "C"

once the next alpha character is found it will be set into field 1 in a new record.

I hope this better explains what I am trying to do.

Thanks again for you help!

Joe T.

[sbg2]

Let ( X ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Middle(X;BiggestAlpha+1;1))

In short.... In string [color:"red"] X go to position [color:"red"] BiggestAlpha+1 and return a string that is [color:"red"] 1 character long. If BiggestAlpha = 1 (A) then this calc returns B, if the calc was Middle(X;BiggestAlpha+1; [color:"red"] 2 ) it would return BC.

If you want the calc to "wrap around" if BiggestAlpha is 26(Z) then change X to ="ABCDEFGHIJKLMNOPQRSTUVWXYZ [color:"red"]A"

Edit: A more efficient way to write the calc would be:

Let ( X ="BCDEFGHIJKLMNOPQRSTUVWXYZA"; Middle(X;BiggestAlpha;1))

[Joe Troxtel]

Man I love this forum... You are the greatest!!!

It works perfect!

Thanks for your Help!

Joe T.