numerical field with text

[KirstyB]

I have an ID system in my database which consist of 2 letters (always the same 2 letters) then numbers (which go up 1 increment for every client in the database....ie TB1, TB2, TB3 etc . i want to sort my db so that it is in order of numbers however I am unable to do this as the sort function rads it as text and organises it in order of TB1, TB10, TB100, etc how do i sort this?

[Reed]

You could create a calculation number field that extracts all of the characters to the right of the first two (TB), then sort the records based on that field. You would use the Length() function to determine the length of the string. Then assuming you always had two characters before the numbers, you would use the Right () function to get the trailing digits.

Right ( idFieldName ; Length ( idFieldName ) - 2 )

Would return the characters you want. Just make sure that the popup menu in the calc dialog is set to return a number, not text. That way it will sort based on the numerical values.

Another option is to just use numbers for your ID instead of text. Since the TB is always the same, you really don't need it in your ID. If you really want to display that text. Just create a calculation text field

"TB" & idFieldName

that concatenates the two things.