Sorting using different year format types

[Guest]

We have a user who wants to sort information by year, but there is a catch.
This user's information looks like so (only year field shown)

1999 (full year)
0305 (2 digs for month and 2 digs for year)
0205
0506
0405

Bascially, this user wants the sort to look like so;

1999
0205
0305
0406
0506

These are all years, but they are formated different. I asked the user if
they could change the format of the 1999 to something like 0199 and she did
not like that idea because the user wants to keep the integrity of the data.

So... is there any way to sort items the way that you want? To me logically
speaking the computer sorts for you based upon what makes sense and sorting
1999 above the other digits is okay, but then you ask the computer to sort a
different way when it reaches an item that is formatted different. Hope this
makes sense. Any help would be appreciated.

Thanks

 

[Rick Brandt]

We have a user who wants to sort information by year, but there is a
catch. This user's information looks like so (only year field shown)

1999 (full year)
0305 (2 digs for month and 2 digs for year)
0205
0506
0405

Bascially, this user wants the sort to look like so;

1999
0205
0305
0406
0506

These are all years, but they are formated different. I asked the
user if they could change the format of the 1999 to something like
0199 and she did not like that idea because the user wants to keep
the integrity of the data.

So... is there any way to sort items the way that you want? To me
logically speaking the computer sorts for you based upon what makes
sense and sorting 1999 above the other digits is okay, but then you
ask the computer to sort a different way when it reaches an item that
is formatted different. Hope this makes sense. Any help would be
appreciated.

Thanks

Didn't you ask this before?

In a query include all your current fields plus a calculated field...

SortVal: IIf([FieldName] = 1999, 0, [FieldName])

Sort on that field.

 

[John Vinson]

We have a user who wants to sort information by year, but there is a catch.
This user's information looks like so (only year field shown)

1999 (full year)
0305 (2 digs for month and 2 digs for year)
0205
0506
0405

Bascially, this user wants the sort to look like so;

1999
0205
0305
0406
0506

These are all years, but they are formated different. I asked the user if
they could change the format of the 1999 to something like 0199 and she did
not like that idea because the user wants to keep the integrity of the data.

The data HAS no integrity. Data should be homogeneous - and this
isn't.

All she's keeping is compatibility with a 1950's style bit-packing
kludge.

So... is there any way to sort items the way that you want? To me logically
speaking the computer sorts for you based upon what makes sense and sorting
1999 above the other digits is okay, but then you ask the computer to sort a
different way when it reaches an item that is formatted different. Hope this
makes sense. Any help would be appreciated.

Try a calculated field:

SortKey: IIF(Left([YearField], 2) = "19", [yearfield] & "00", "20" &
Right([YearField], 2), Left([YearField], 2))

This will convert this mess to numbers like 199400, 199700, 200502,
200503, 200604 and so on - which will sort chronologically.


John W. Vinson[MVP]

 

[Guest]

John,

This worked like a charm. I did have to change the comma between the last
two arguments to an '&', but all else was great. Thanks so much!
--
Scott

We have a user who wants to sort information by year, but there is a catch.
This user's information looks like so (only year field shown)

1999 (full year)
0305 (2 digs for month and 2 digs for year)
0205
0506
0405

Bascially, this user wants the sort to look like so;

1999
0205
0305
0406
0506

These are all years, but they are formated different. I asked the user if
they could change the format of the 1999 to something like 0199 and she did
not like that idea because the user wants to keep the integrity of the data.

The data HAS no integrity. Data should be homogeneous - and this
isn't.

All she's keeping is compatibility with a 1950's style bit-packing
kludge.

So... is there any way to sort items the way that you want? To me logically
speaking the computer sorts for you based upon what makes sense and sorting
1999 above the other digits is okay, but then you ask the computer to sort a
different way when it reaches an item that is formatted different. Hope this
makes sense. Any help would be appreciated.

Try a calculated field:

SortKey: IIF(Left([YearField], 2) = "19", [yearfield] & "00", "20" &
Right([YearField], 2), Left([YearField], 2))

This will convert this mess to numbers like 199400, 199700, 200502,
200503, 200604 and so on - which will sort chronologically.


John W. Vinson[MVP]