Sort (order) by question

[batteam]

I have a field in an Access 2000 report detail section that is not bound to the record source. Instead it gets its data from code behind the report - in the 'detail_format' section. For example, I have a bound field in Medium Time format displaying time of day, and I use code (a Select Case stmt.) to calculate a new time in my unbound field based on a time zone code and the time in the bound field. My problem is, I can't get the report to 'order by', in ascending order, on this new, calcualted time field.

Is it because the unbound field data isn't defined until the detail section of the report is composed? Using the 'Group By' dialogue box or entering the unbound text box's name in the report's property box (Order by) doesn't seem to do the trick. Any suggestions on how or if this can be done, besides defining the data in the record source itself, would be appreciated. Thanks in advance.

 

[THWatson]

batteam
I think that, as you suggest, the problem lies in the fact that the calculated text box isn't known until after the Detail section is formatted, and by then it's too late for Access to know how to use it for ordering purposes.

Tom

 

[CosmoKramer]

batteam,

Can you change your Select Case statement to an embedded IIf statement?? An IIf statement will work in the Field/Expression section of the Sorting and Grouping dialog box. You have to repeat the calculation logic there, not refer to the calculated control....

Si hoc legere scis, nimis eruditionis habes

 

[BSman]

Make your data source for the form a query than includes a new field (I'll call it "LocalTime") that does the calculation of the local time for each report. Not only can you display that data on your form (instead of calculating it each time on the form), but, more import, you can then sort on that new field as part of your query.

Bob