Thread: sort alpha numeric

The following query works; select max(cint(mid([cus:number],3,len([cus:number])))) as maxnum from customer where [cus:number] like 'H*' FYI for others that might be having the same problem. "seeker" wrote: I have a field which lists customer account numbers as A-1, A-2, Through A-999 if there are that many customers that start with A. I need to find the maximum number in this text field so that I can add 1 to...

The following query works; select max(cint(mid([cus:number],3,len([cus:number])))) as maxnum from customer where [cus:number] like 'H*' FYI for others that might be having the same problem. "seeker" wrote: I have a field which lists customer account numbers as A-1, A-2, Through A-999 if there are that many customers that start with A. I need to find the maximum number in this text field so that I can add 1 to...

On Tue, 27 Oct 2009 18:20:01 -0700, seeker wrote: I have a field which lists customer account numbers as A-1, A-2, Through A-999 if there are that many customers that start with A. I need to find the maximum number in this text field so that I can add 1 to create a new account number for a new customer. I have tried max(mid([cus:number]), 3,len([cus:number])) in a aggregate query and that does not return the max number after letter and dash...

Because of the title of your post, I'm assuming that you need to sort on the account numbers. Do you realize that with this numbering scheme, all account numbers with first 3 characters A-1 will sort ahead of numbers beginning A-2, e.g. A-1 A-10 A-11 all other numbers in the form A-1 followed by any number of digits A-2 A-20 etc. -TedMi "seeker" wrote in message ... I have a field which lists customer account numbers as...

On Tue, 27 Oct 2009 18:20:01 -0700, seeker wrote: I have a field which lists customer account numbers as A-1, A-2, Through A-999 if there are that many customers that start with A. I need to find the maximum number in this text field so that I can add 1 to create a new account number for a new customer. I have tried max(mid([cus:number]), 3,len([cus:number])) in a aggregate query and that does not return the max number after letter and dash...

Because of the title of your post, I'm assuming that you need to sort on the account numbers. Do you realize that with this numbering scheme, all account numbers with first 3 characters A-1 will sort ahead of numbers beginning A-2, e.g. A-1 A-10 A-11 all other numbers in the form A-1 followed by any number of digits A-2 A-20 etc. -TedMi "seeker" wrote in message ... I have a field which lists customer account numbers as...