job.number = JobPart#;
alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
mod.number = Mod ( job.number ; 26 ) ;
adj.mod.number = If ( mod.number = 0 ; 26 ; mod.number ) ;
letter = Middle ( alpha ; adj.mod.number ; 1 ) ;
div.number = Div ( job.number - 1 ; 26 ) ;
number = If ( div.number > 0 ; div.number ) ;
result = If ( job.number > 0 ; letter & number )
Here is my version of the formula you're looking for;
Char ( Mod ( PartNo - 1; 26 ) + 65 ) & Case ( PartNo > 26 ; Div ( PartNo - 1 ; 26 ))
This works up to PartNo '260' = 'Z9', before it adds another digit.
So PartNo '261' = 'A10' and this formula will continue to auto-increment the digits as required.
Best wishes - Alan Stirling, London UK.
I trust you are not using this human readable identifier field in relationship matching. Just a thought!
Oh, goodness, no. I have a unique __pk_TableName_ID field for each table, that does not get touched. That ID field is also generally hidden from my users, because to them, it means nothing… and I have other, "human readable" numbers and identifiers that they actually interact with.
That worked beautifully. Thank you!
Although I shudder at the thought of a JobTicket with 260+ parts to it… at least the solution is ready for it this way.