Sort Order

[Antonio]

I have a Access database used by several users. Unique, 6
digit numbers are entered into the database and print out
on various reports for various reasons. The we sort the 6
digit numbers (each number corresponds to a patient and
his/her medical chart) in a unique way. Is there a way to
specify the sort order for numbers entered into an access
database?
Example: MR # 567891 is phsically filed by the last two
digits, then the middle two digits, and finally the first
two digits.
So...for sorting purposes it would be nice if you could
tell access to sort like this: 56 78 91
3 2 1
To make it work, I have had to break each 6 digit MR
number into 3 individual, 2 digit, fields and place them
right next to each other on all forms. Then I sort by the
last two digits only...
I appreciate any help.

 

[TC]

Add a new field "SortOrder" to the relevant table(s). Have each form that
can add new records to that table, use the BeforeUpdate() event to populate
that field, when each new record is created. Populate that field with a
value that sorts in the desired order. For example, when the form adds a new
record with ID "MR # 567891", set the value of the SortOrder field to
"917856". Then you can sort your reports etc. on the SortOrder field.

HTH,
TC

 

[Steve Schapel]

Antonio,

In the query that the form is based on, make a calculated field to be
used as the basis of your sorting. This field does not need to be
displayed on the form if you don't want it to. Like this...
Sorter: Right([MR],2) & Mid([MR],3,2) & Left([MR],2)

By the way, just a hint for the future... If you really feel you need
to post to more than one newsgroup (in practice, this is seldom
necessary), please cross-post (i.e. send the same message to both
groups simultaneously) rather than multi-post (send repeat copies of
the message to each group). Thanks.

- Steve Schapel, Microsoft Access MVP