I want to have a field, maximum length 999.99, with the ability to not use all of the character positions yet have leading zeroes. In other words 02.2 would be acceptable as well as 2. I want it to sort numerically so making the field text to accept the leading zeroes doesn't allow it to sort correctly. I'm using A2K.

Thanks in advance, Tom

Do you want to:

1) Allow the entry of 02.2 and 2.2 but keep everything as a number?

2) Keep everything as 002.20, 099.99, 999.99 but sort records as if the data was numeric?

3) Do you always wanted to display value with packed zeros or not?

Door number 1. They are using the leading digits, only in some cases, to designate the year so 02.2 would be the second record for 2002. In other cases it's not tied to the year so 2.2 or even volume 2 would be appropriate. I don't want to force leading zeroes but would like to let the user decide if they are needed. Still, it needs to sort numerically.

Thanks for the quick response.

Tom

If someone enters 02.2, do you want it to display 02.2 (now an forever) and if someone enters 2.2, do you want it to display 2.2, and last but not least, are these two entries to be different values?

Yes to all.

Tom

To do what you want, you'll need to either

1) Keep two fields, one text field, used for entry and display, and a second field, a numeric field, used for sorting. PLEASE NOTE THAT THIS WILL RESULT IN A DENORMALIZED TABLE, however it will sort faster. You will need to write code on the after update event on the text field to create the numeric value. This is easy, just,

NumericField = Val(TextField)

2) Create a query to sort the records with a calculated field

SortField:val(TextField)

Then sort of the SortField, not the TextField. This is the technically preferred way to do this, however it will sort slower, especially for large recordsets.


Your choice!

Thanks, Jay, I'll give those a try later this afternoon. I like the calculated field method and for this application I don't think the recordset will be over a hundred records or so.

Thanks again, Tom