My solution has invoice numbers with five digits followed by a letter. I'd like to let my users preview what the subsequent invoice number in a particular series would be, before it's created (why? Long Story).

In other words, if invoices 20000a, 20000b, and 20000c exist, then I want a tooltip to show that the next invoice to be created in the 20000 series would be 20000d. Auto-enter serial numbers are not being used, btw.

I'm working on a formula that's getting me close:

Let ( [

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( "abcdefghijklmnopqrstuvwxyz" ; trailingLetter ; 1 ; 1 ) ;

trailingLetterPlusOne = trailingLetterAsNumber + 1 ;

subsequentLetter = GetAsText ( trailingLetterPlusOne ) ] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." )

I need to get the next-to-last line of this formula to convert the number to a letter. Obviously, GetAsText isn't going to work, and I don't know what will.

Hope this makes sense; Suggestions greatly appreciated. Many thanks--

How about:

Let ( [

alpha = "abcdefghijklmnopqrstuvwxyz" ; 

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( alpha ; trailingLetter ; 1 ; 1 ) ;

subsequentLetter = Middle ( alpha ;  Mod ( trailingLetterAsNumber ; 26 ) + 1 ; 1 )

] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." 

)

Edited April 12, 200817 yr by Guest
Another typo

Try this:

Let ( [

trailingLetter = Right ( invoiceNumber ; 1 ) ;

trailingLetterAsNumber = Position ( "abcdefghijklmnopqrstuvwxyz" ; trailingLetter ; 1 ; 1 ) ;

trailingLetterPlusOne = trailingLetterAsNumber + 1 ;

subsequentLetter = Middle ("abcdefghijklmnopqrstuvwxyza"; trailingLetterPlusOne );1 ] ;

Left ( invoiceNumber ; 5 ) & subsequentLetter & " is the next invoice in this series." )

You still need to look after the situation where the current number ends in "Z", but this should get you going.

Thank you so much for your quick replies and useful suggestions, Comment and BobWeaver. Problem solved! Much appreciated.

We aim to please.