Sorting out shifts in VBA
Comments (0) 
At the company where I work we run three shifts which run in the order 3,1,2. This is common at a lot of companies since 3rd shift usually starts somewhere around midnight, which marks the beginning of the day. On many of the reports we produce from Microsoft Access it is required that we sort the shifts in that order. For awhile I used an If…Then…Else statement which would return shift 3 as shift 0, which would place it in the correct order. I would call the resulting field ‘ShiftSort’ and sort on that field in ascending order while using the original shift field for display purposes on the report.
Later on I was thinking about the problem and whether or not there might be a more elegant way of dealing with it. I realized there was - the same result could be had by inserting the following into a field in my query:
SortShift: [Shift] MOD 3
This assumes the shift field is named Shift. The Modulus (MOD) operator in VBA is used to find the remainder of a division operation. 1 and 2 cannot be divided by 3, so they are returned unchanged. 3 is divisible by 3, and the remainder of course is 0. Thus, this simple one line of code produces the exact result I need to sort the shifts properly.
I also considered how to handle a company that starts with 2nd shift, for which the following would do the job:
SortShift: [Shift] + [Shift] MOD 3
The results for shifts 2,3 and 1 are 4, 3 and 2, respectively. In this case, you would sort the resulting ShiftSort field in descending order. It’s a little messier, but I haven’t worked out a more elegant way to accomplish this, and I don’t think it’s very common to start with 2nd shift anyways.
Photo: Time Card by flickr user TheGoogly, used under CC license.
