Hi,
Is there a way to easily substitute a letter for it's corresponding position in the alphabet.
So A would turn into 1, D into 4, M into 13 etc.
Obviously I could do some convoluted CASE, but I was trying to avoid that.
Alternatively, I may be on completely the wrong track, so here's my initial problem.
I'm creating a unique reference for a person by using their account number and then joining on their position on the account.
This had been working fine, but recently I've had an issue where more than 9 people exist on an account, and the system this is based on then decides to use letters for the person's position (so it goes 1 - 9, and then moves onto A - Z!)
So my first thought was to find the position of the letter in the alphabet and then add 9 to it (so A would become: 1 + 9 = 10) which would then fit in with the current pattern!
Any ideas most welcome!
Is there a way to easily substitute a letter for it's corresponding position in the alphabet.
So A would turn into 1, D into 4, M into 13 etc.
Obviously I could do some convoluted CASE, but I was trying to avoid that.
Alternatively, I may be on completely the wrong track, so here's my initial problem.
I'm creating a unique reference for a person by using their account number and then joining on their position on the account.
This had been working fine, but recently I've had an issue where more than 9 people exist on an account, and the system this is based on then decides to use letters for the person's position (so it goes 1 - 9, and then moves onto A - Z!)
So my first thought was to find the position of the letter in the alphabet and then add 9 to it (so A would become: 1 + 9 = 10) which would then fit in with the current pattern!
Any ideas most welcome!