Sorting on a calculated field in a report

[puzzledinsc]

11/28/2002 question, theSizz asked about sorting on a calculated field in a report. Answer from danvlas & MazeWorx was to put calculation in a query and sort on the resulting report input field. This is fine is the calculation is a simple math process. I have a 2 page vb code calculation for predicting a Retirement date using 'Date of Birth, 'Service Start Date', Retirement System in a if...then...else and case procedure and function. These calculations can't be in a query which returns to the original question. Is there no way of sorting data when printed out on a calculated field? i.e. on the 'Retirement Eligible' field?

 

[scriverb]

You should still do it in a query but take you VBA code for determining the Retirement Date and put it in a new Function in a database module. That way you can make the call from the query with the appropriate parameters for the individual included and your 2 pages of VBA code will then return the appropriate Retirement date.

Now you can sort on the control in your report using the Sorting and Grouping process.

Bob Scriver