Custom sort

[sl]

I have a query with the following calculated field:
Status: Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active". I want to sort it so that
Expired is first, Unknown is next and Active is last but
don't know how. Help is much appreciated.

 

[Allen Browne]

Type two more calculated fields into the query grid:
(([DisclosureExpiration]<Date()) Or ([ConfidentialityExpiration]<Date())
and
(([DisclosureExpiration] Is Null) And ([ConfidentialityExpiration] Is
Null))

In the Sorting row under these fields, choose:
Ascending

 

[fredg]

I have a query with the following calculated field:
Status: Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active". I want to sort it so that
Expired is first, Unknown is next and Active is last but
don't know how. Help is much appreciated.

Any self-described sort needs to be given a sort order that Access
understands.

Assuming your [Status] field is returning the correct values, add
another column to the query.

SortThis:IIf([Status]="Expired",1,IIf([Status]="Unknown",2,3))
and sort on this column.

Be aware that if this query is to be used as a record source for a
report, the sort order in the query will be irrelevant to the sort
order of the report.
You must set the report's sort order in the report's sorting and
grouping dialog.

 

[Guest]

This is my code now:
SELECT Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active") AS Status,
tblLegalHistory.AgreementType,
tblLegalHistory.EffectiveDate,
tblLegalHistory.DisclosureExpiration,
tblLegalHistory.ConfidentialityExpiration
FROM tblLegalHistory
ORDER BY (([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date())),
(([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is Null));
But, I know have all Unknowns, followed by a group of
Active where Confidentiality is blank, then the Expired,
then the rest of Active. What am I doing wrong?

-----Original Message-----
Type two more calculated fields into the query grid:
(([DisclosureExpiration]<Date()) Or ([ConfidentialityExpiration]<Date())
and
(([DisclosureExpiration] Is Null) And

([ConfidentialityExpiration] Is

Null))

In the Sorting row under these fields, choose:
Ascending

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query with the following calculated field:
Status: Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active". I want to sort it so that
Expired is first, Unknown is next and Active is last but
don't know how. Help is much appreciated.

.

 

[Allen Browne]

My bad: we need to handle the null question first.

Because there are some unhandled specifics with null in the expression, you
may prefer to repeat the Switch() in the ORDER BY clause, but this time
generate a number to sort by:

ORDER BY
Switch((([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),1,
(([DisclosureExpiration] Is Null) And ([ConfidentialityExpiration] Is
Null)), 2, True, 3)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is my code now:
SELECT Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active") AS Status,
tblLegalHistory.AgreementType,
tblLegalHistory.EffectiveDate,
tblLegalHistory.DisclosureExpiration,
tblLegalHistory.ConfidentialityExpiration
FROM tblLegalHistory
ORDER BY (([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date())),
(([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is Null));
But, I know have all Unknowns, followed by a group of
Active where Confidentiality is blank, then the Expired,
then the rest of Active. What am I doing wrong?

-----Original Message-----
Type two more calculated fields into the query grid:
(([DisclosureExpiration]<Date()) Or ([ConfidentialityExpiration]<Date())
and
(([DisclosureExpiration] Is Null) And

([ConfidentialityExpiration] Is

Null))

In the Sorting row under these fields, choose:
Ascending

I have a query with the following calculated field:
Status: Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active". I want to sort it so that
Expired is first, Unknown is next and Active is last but
don't know how. Help is much appreciated.