Serial Number to Alpha

[bigchiefs]

I need to convert a serial number (01,02,03...) to an Alpha? i.e. 01=A, 02=B,...10=J,...26=Z. I have a portal set up which creates new line items for a specific Job# (related by Job#). I have a couple of calculation fields in the related file that give each related entry a unique serial number (-001, -002,...).

In my master file I want a "Line Item" field. It is simple enough to have a calculation using the Right function to get a serial (01,02etc) and I can convert this to Alpha using a longwinded Case statement. Is there a simpler way of achieving this

[Ocean West]

Choose(

Right(JobNumber;3) ; "" ;

"A";

"B";

"C";

"D";

// fill in the rest here

"X";

"Y";

"Z")

However this would limit you only to 26 Jobs MAX..

Edited December 15, 2005 by Guest

[Lee Smith]

I'll add to this by saying that if you are us the US version of 6, you need to change the ; to , (semicolons to commas).

Choose(

Right(Number,3) , "" ,

"A",

"B",

"C",

"D",

"E",

"F",

"G",

"H",

"I",

"J",

"K",

"L",

"M",

// and so on....

"Y",

"Z")

[bigchiefs]

Many thanks, worked a perfectly, certainly less longwinded than a Case argument

[LaRetta]

Hi bigchiefs,

I think I'd cheat ... I'd use a global text or a single text field in Preference (field called TEXT) to hold the values (in multiline) as:

A

B

C ... Then the calc (serial would be your serial field) would simply be:

MiddleValues ( TEXT; serial ; 1)

The line count becomes the result. You could even hit AA BB etc if you wished. Your 'calculation' then wouldn't be limited to your field definition ... certain personnel could add the letters at the bottom and it'd stretch forever for you, handled in Browse mode.

Ooops - you are vs. 6. You would need to use: MiddleWords( TEXT, serial, 1)

LaRetta

Edited December 15, 2005 by Guest
Added calc for vs. 6

[Ocean West]

Don't for get to substitute the trailing ¶ with Nothing

(i like that idea Laretta) :