Conference bump::msaccess

1656.0. "My own sorting for numbers stored as chars?" by AWECIM::HOWARD () Tue Apr 29 1997 20:18

I am using ODBC to link to an Rdb database.  For several reasons, the
database design calls for integers to be stored as characters in the Rdb
database for a particular field called Operation.  The MS-Access form is
designed to show the fields in a datasheet sorted by Operation.  

Unfortunately, what happens is that the characters are not sequenced as
if they were numbers, as in:
	111
	1111
	1112
	1113
	112
	1121
	1122
	1123
	etc.

Are there any suggestions?  One idea being considered is to force the server
writing to Rdb to pad the strings of less than four characters with leading 0's,
resulting in:
	0111
	0112
	1111
	1112
	1113
	1121
	1122
	1123
	etc.

If I do this, I guess I'll need to force leading zero's on user input into
MS-Access as well.

Is there an alternative?  Perhaps some way to have Access use a sorting
algorithm I write in basic?

Thanks,
John Howard

    
    	Create a field in a Make Table query that looks like this:
    
    	numeric_equiv:[text_integer]
    
    	and set the numeric_equiv field to an integer in the table, then
    	you can do your sorting and reporting on the numeric field and
    	still keep the text field intact.
    

Simpler would be to base the form on a query that is sorted correctly.
ie

SELECT * FROM My_Table ORDER BY CLng(My_Table.My_Field)