I have an Access 2002 db where in one table, the order table, we have recently crossed into the 10,000s. This makes the forms and reports on these orders sort funny because some of the orders from other furniture lines start with alpha characters and there are dashes in some orders, so the OrderNo field is text. Does anyone have any ideas on how to get the associated forms and reports to sort like I would like them--ie 9999 before 10000 not after it? Thank you in advance for your time.
In your QBE grid of your query (the bottom where your fields are listed), remove your OrderNo field and add the following in a blank column:
"OrderNoInt" can be any name you want. It's will be the name of the field that shows up in the query. I don't think you can use an existing fieldname there. If you want to try it and double-check, just put this instead: OrderNo: cInt(OrderNo)
If it gives you an error, try a different label in front of the colon (like the first one I wrote at the top).
The cInt() function converts your "text" number to an integer value on the fly before sorting your query without altering the actual data or the underlying table format. Then they'll sort in correct order.
It won't work if you have records in your query result set that have characters in that field though.
Thanks for your help but I figured out how to do it by using the val() function and sorting first by it, then by the OrderNo. The problem is that there will always be characters in the result set, so in the past I've had them put 0's in front of the number to get the right amount of characters, but I wish now that I would have thought of this method first... oh well, live and learn
For efficiency, it may be very desirable to put a separate numeric column, e.g. "intOrderNumber," which contains the numeric equivalent of the order-number. This field can be populated each time a new order is created or changed; it can be initially populated with an Update query.
A field like this can be indexed (with a non-unique index) to permit very efficient searching and sorting. While you won't see any real effect with "only" 10,000 records, as the database continues to increase in size it may become more useful.