Thanks for clarifying these points - it really helps with understanding what you are doing. Your approach appears very sound, but not quite complete. You will also have to include a field in your query to group your report on whether you are using Odom or not. Otherwise, sorting is likely to mix the two together without grouping all zero-odometer readings into one group sorted by date value and the other group sorted by odomoeter reading. You could add a simple true/false field for this in your query such as
Other point is that to sort on a single field correctly I think you should convert your dates to numbers in the IIF for sorting purposes. That way the sort in your subsequent report will sort on numbers only. The IIF would be changed to
- SortBy: IIF([odom]=0, CLng([TripDate]), [Odom])
With this slightly revised SortBy field and a new grouping field of UseDateSort you should use grouping in your report on UseDateSort before sorting on SortBy.
Hope you succeed with this.
-Stewart