sort alpha numeric

  • Thread starter Thread starter seeker
  • Start date Start date
S

seeker

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 are stripped. Need some ideas thanks.
 
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 are stripped. Need some ideas thanks.
Click to expand...

Try:
Val(Mid([FieldName],InStr([FieldName],"-")+1)
 
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.
 
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
 
Back
Top