From an Access table of Postal Codes, I am trying to run a report in Crystal 11 that will display the minimum and maximum ranges of the postal codes, but not include any invalid ranges in the min & max.
Example:
{Postal Code}
V6M0A1
V6M1A1
V6M1A2
V6M1A3
V6M1A4
V6M1A5
V6M1A6
V6M1A7
V6M1A8
V6M1A9
V6M1B2
V6M1B3
V6M1B4
V6M1B7
V6M1B8
V6M1B9
V6M1C1
V6M1C2
V6M1C3
V6M1C4
V6M1C5
V6M1C7
V6M1C8
V6M1C9
The result required is:
V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B4
V6M1B7 to V6M1B9
V6M1C1 to V6M1C5
V6M1C7 to V6M1C9
I have tried grouping on a formula:
Left ({POSTAL_CODE},5 )
and then using min & max summaries but the resulting ranges then include
values that don’t exist:
V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B9
V6M1C1 to V6M1C9
Can anyone steer me in the right direction?
Example:
{Postal Code}
V6M0A1
V6M1A1
V6M1A2
V6M1A3
V6M1A4
V6M1A5
V6M1A6
V6M1A7
V6M1A8
V6M1A9
V6M1B2
V6M1B3
V6M1B4
V6M1B7
V6M1B8
V6M1B9
V6M1C1
V6M1C2
V6M1C3
V6M1C4
V6M1C5
V6M1C7
V6M1C8
V6M1C9
The result required is:
V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B4
V6M1B7 to V6M1B9
V6M1C1 to V6M1C5
V6M1C7 to V6M1C9
I have tried grouping on a formula:
Left ({POSTAL_CODE},5 )
and then using min & max summaries but the resulting ranges then include
values that don’t exist:
V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B9
V6M1C1 to V6M1C9
Can anyone steer me in the right direction?