Subject Re: indexing problem From Mervyn Bick <invalid@invalid.invalid> Date Sun, 10 Jul 2016 09:59:32 +0200 Newsgroups dbase.getting-started On 10-Jul-16 2:12 AM, Charlie wrote: > Using golfers.. If I tag group only the index is correct in it goes 1,2,3,4,5,6, etc... > > However if I index on group, lname tag group indexing is 10, 11,1,2,3,4,5,6,7 etc... > > So not sure why it does this when you index on group then lname???? Any explanation?  Any fix?  Thanks.... > dBASE is auto-converting the contents of the numeric field "group" to character and then building the index expression.  1 is converted to '1', 10 is converted to '10', 2 is converted to'2' and so on. When the names are concatenated to build the index expression '10xxxx ' comes before '11xxxxx    ' which , in turn, comes before '1xxxx    '. If you had a large enough table all the "twenties" would come before 2, all the "thirties" would come before 3 and so on. Apart from not giving you the expected order this also violates the first rule of Indexes which is that index expressions must always have the same length for a given index.  This is why one NEVER uses TRIM() when creating an index. Instead of letting dBASE auto-convert the numeric value you need to do it yourself by using the str() function.     Index on str(group)+name tag whatever Your index expressions will now be '         1xxxx   ', '         2xxxxx      ' and so on which will give you the expected order. You can create a simple index on a date without a problem but if you ever need to include a date in a compound index you will need to convert it to a character string.  You should use dtos() as this converts the date to 'yyyymmdd' which will give you a proper chronological order. GROUP is a reserved word in SQL so it should not be used as a field name.  It will give you a problem (which is not insurmountable but is a nuisance) if you start using the dBASE report engine. Mervyn.