Can I create 'conditional' sorting on report

[ncsthbell]

I have 2 columns, Date & Odom which I need to base the sorting on. If the column 'odom' has value of '0', I want to use the 'Date' field to sort the report, if there is a value in the odom column, I want to use 'Odom'. I created a invisible text box on my report called 'txtsortparm' and in the control source I used: =IIF([Odom]=0,[Date],[Odom]). The report is prompting me for a value for this column. Any suggestions on how I can do 'conditional sorting'???

Thanks!

[Stewart Ross]

Hi. Could you explain what you mean when you say 'if the column 'odom' has the value of '0'? If Odom is a field in the table on which you are basing your report you will not be able to sort using the IIF, as you are actually comparing values on a row-by-row basis.

It would be helpful to see some sample data to get a clearer picture of what you are trying to sort upon.

-Stewart

[ncsthbell]

Sorry, here are some more details. I am using a query for my report data source. For each row of data there are columns named 'Odom' and 'TripDate'. I have the sort/grouping on the report set up currently to sort by 'Odom' (this value is the beginning odometer #). I have been asked to change the report so the sort will first check to see if the value of 'Odom' is 0 (no beginning odom exists), if is 0, then they want me to use the 'trip date' to sort the details.
I tried setting up a new column in my query and put the "if" test in the query and set a new field called 'SortBy'. I moved the value of either Odom or TripDate (depending on if Odom was 0) to this new column. Then I put this column on the report as invisible and changed the sorting/grouping on the report to use this value. It did not work.
I hope these details make more sense as to what I am trying to accomplish.

[Stewart Ross]

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

UseDateSort: [Odom]=0

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