Sorting a table on text field as if numeric

[P E Schoen]

I just received a request from a customer who wants to sort test results
according to an "ID" field, which is set up as 20 characters in a dBase III+
database using the BDE and D4pro. For instance, he wants the results ordered
as follows:

124
1231
1234

Probably the easiest solution would be to change 124 to 0124. Apparently
this customer has used a simple sequence of numbers, possibly starting from
1 and going to (at least) 1234 per his example. If this were a one-off
application I could change the field to numeric, or I could convert any
numeric entry into the equivalent text with leading zeroes to four or five
digits.

Generally, "the customer id always right", but I think this request borders
on the unreasonable.

Any ideas or advice to present to the customer?

Thanks,

Paul

[P E Schoen]

(Solved but posted for reference)
I just encountered a weird problem as I was testing this. If I use a query
like:

SELECT * FROM ORTRES
WHERE RECLID > " "

It works as expected, and I can still edit the RECLID or other fields in
DBGrid component. But if I add the following:

Order by RECLID DESC

The DBGrid component does not allow editing.

And if I perform another operation on a record, triggered by a button on the
form, I get the error:

qRes: Cannot modify a Read Only Dataset.

OK, I found this in the Help files:

Single-table queries:
Queries that retrieve data from a single table are updatable provided
that:
There is no ORDER BY clause.

I'll have to add an alert to notify the user if the SQL statement causes the
query to lose the Updateable property. But I could also add an index:

"The read-only effect of an ORDER BY clause is negated and the query
updatable if the ORDER BY clause uses a single column and there is a dBASE
single-column primary or secondary index based on that same field."

No need to respond unless you have any ideas about the ordering per customer
request. I found that padding with zeroes or spaces work equally well.

Thanks,

Paul

[a***@aol.com]

Post by P E Schoen
I just received a request from a customer who wants to sort test results
according to an "ID" field, which is set up as 20 characters in a dBase III+
database using the BDE and D4pro. For instance, he wants the results ordered
124
1231
1234
Probably the easiest solution would be to change 124 to 0124. Apparently
this customer has used a simple sequence of numbers, possibly starting from
1 and going to (at least) 1234 per his example. If this were a one-off
application I could change the field to numeric, or I could convert any
numeric entry into the equivalent text with leading zeroes to four or five
digits.
Generally, "the customer id always right", but I think this request borders
on the unreasonable.
Any ideas or advice to present to the customer?

I'm not a "hot" database person, but could you have a calculated field
(which would be in fields in your code, not in the database) produced
by StrToInt(), on which you sort.

Alternatively if the sort mechanism is under your control in a sort
function (as in TStringGrid) then code to do the calculation in the
sort function.

Alan Lloyd