I have a DB for tracking time and expenses. I use a calculated field to ID week number. A summary report showing hours by week number is useful for monitoring weekly hours. Both the summary and cross tab reports order week numbers as 1, 10, 11, 12…2, 20, 21, 22, 3, 30, 31, 32, 33, etc. Is there any way to have the week numbers (rows) display in order, rather than what currently happens?
Change the ID field from Text to Numeric (Integer).
The data is displayed in alphabetical order, there is no option to display it in numerical order.
It sounds like you are saying you are using a formula to calculate the week number, perhaps from a date field. So one possibility would be to make the Week field a text field, then make sure that a leading zero is added, so instead of 1 it will be 01. Then the order will be correct.
There are a couple of ways the leading zero could be added, here is one method:
pattern(val(datepattern(“1/1”,“ww”)),"##")
Another option would be to not use the Summary wizard, and instead embed the table into a form. Then you can directly customize the summarytable( function to include whatever formula you want. If you don’t need it elsewhere, you would not even need to set up the separate Week field in this case, since it could be calculated on the fly.