Sort calculated field

[Guest]

In a query I have a field that calculates the age.
Age: IIf([CloseDt] Is Not Null,"CLOSED",(Date()-([BCBSreceived])))

I need to shows these in Decending, I assume it's a format issue, because it
returns:
CLOSED
CLOSED
88
87
753
71
70

How can I make a calculated field sort?????

 

[Guest]

Sort on this field instead. You can uncheck it so that it doesn't show. Still
show your field below.

SortOn: CDate(IIf([CloseDt] Is Not Null,#1/1/1800#,[BCBSreceived]))

 

[John Spencer]

You'll need another column to sort by. Something like the following
AgeSort: IIf([CloseDt] Is Not Null, Null,(Date()-([BCBSreceived])))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I never would have figured that out - thanks...

Sort on this field instead. You can uncheck it so that it doesn't show. Still
show your field below.

SortOn: CDate(IIf([CloseDt] Is Not Null,#1/1/1800#,[BCBSreceived]))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In a query I have a field that calculates the age.
Age: IIf([CloseDt] Is Not Null,"CLOSED",(Date()-([BCBSreceived])))

I need to shows these in Decending, I assume it's a format issue, because it
returns:
CLOSED
CLOSED
88
87
753
71
70

How can I make a calculated field sort?????