How to sort records on a form

[usaccess]

Hi,

I have a form/subform. There is a priority field and then a case status
field. I want to display records so that it sorts by case status as
"actives" then "on holds" then "completes" and then "cancelled"

Then within those cases it should sort by priority.

I know how to sort ascending and descending on forms.

thanks a lot for your help.

regards
us

[pietlinden]

us******@gmail.com wrote:

Hi,

I have a form/subform. There is a priority field and then a case status
field. I want to display records so that it sorts by case status as
"actives" then "on holds" then "completes" and then "cancelled"

Then within those cases it should sort by priority.

I know how to sort ascending and descending on forms.

thanks a lot for your help.

regards
us

You would have to create a table that would allow you to emulate your
own sort order.
Create a table, say called "tblCaseStatus" with two fields: StatusNum
(long integer, unique, PK), and Status (text). Then your table would
be

StatusNum Status
1 active
2 on hold
3 complete
4 cancelled

Then just join the two tables by status num (you'd have to include it
in your subform's table/recordsource) and then you could just create a
query and include the field in the rowsource for your subform. Then
you can use it to sort.

[Wayne Gillespie]

On 18 Jul 2006 13:42:06 -0700, us******@gmail.com wrote:

>Hi,

I have a form/subform. There is a priority field and then a case status
field. I want to display records so that it sorts by case status as
"actives" then "on holds" then "completes" and then "cancelled"

Then within those cases it should sort by priority.

I know how to sort ascending and descending on forms.

thanks a lot for your help.

regards
us

Add a calculated sort field to your form's recordsource -

SortField:IIF([case status]='active',1,IIF([case status]='on hold',2,IIF([case
status]='complete',3,4)))

Sort on this field first and then the prioity field.

Wayne Gillespie
Gosford NSW Australia

[usaccess]

Hello,

All thanks for all your help.

I don't know how to sort on two fields. I only know to sort it on one
field.

regards
us
Wayne Gillespie wrote:

On 18 Jul 2006 13:42:06 -0700, us******@gmail.com wrote:

Hi,

I have a form/subform. There is a priority field and then a case status
field. I want to display records so that it sorts by case status as
"actives" then "on holds" then "completes" and then "cancelled"

Then within those cases it should sort by priority.

I know how to sort ascending and descending on forms.

thanks a lot for your help.

regards
us

Add a calculated sort field to your form's recordsource -

SortField:IIF([case status]='active',1,IIF([case status]='on hold',2,IIF([case
status]='complete',3,4)))

Sort on this field first and then the prioity field.

Wayne Gillespie
Gosford NSW Australia