I have a query that lists all the individuals in a particular census and would like to sort it in to correct reference order. The problem is that each of the parts of the reference number (for 1881 Piece, Folio and Page) are variable length. Just sorting on the whole reference id doesn't work because it just does a text sort of the whole string so RG11/567/8/9 would come after RG11/1234/56/7.
I've managed to improve it significantly by extracting the Piece and making it numeric so it sorts correctly using this expression:
Code: Select all
Have I missed the obvious for how to extract the other parts to make them separate numerically sortable columns?