Sorting by complex alphanumeric data

[Sandie]

I have an account number field where the data is formatted as follows:

A-22-95

The first one (or two) characters are always letters. The next set of
characters are numbers. And the third set represents a year. On a report,
I need my account numbers sorted FIRST by the year (in descending order),
then by the letter in alphabetical order, then by the middle number in
ASCENDING order.

For example:

A-21-98
B-22-98
C-40-98
A-20-97
B-19-97

I think I know what you are going to tell me - I have to break up the acct
numbers into 3 separate fields... but is there another way? Or do you have
any tips for handling it?

 

[Pendragon]

You can keep your account numbers as they are. In the source query for the
report, add three fields at the beginning of the field list. Put these in
the field boxes:

SortYear: right$([Account Number],2)
SortBy: Descending

SortOther: left$([Account Number], len([Account Number]) - 3)
SortBy: Ascending

Since both your letter sort and numeric (non-year) sort are ascending, the
one field will sort appropriately - AA-25 will list before CG-56, etc., and A
will list before AA, etc.

If for some reason you need to break up the SortOther, then

SortLetter: left$([Account Number],len(instr([Account Number])) - 1)
SortBy: Ascending

SortNumber: mid$([Account Number], instr([Account Number]) + 1, 2)
Sort By: Ascending

The len() function allows the consideration of either 1 or 2 places for a
letter (A or AA). The SortNumber assumes that your number in the middle of
the account number will always be 2 digits.

In design view of your report, set your Sorting & Grouping accordingly. It
is not necessary to have the Header or Footer for each grouping, nor is it
necessary to have textboxes or fields on your report to represent the sorting
data.

Have fun!

 

[Marshall Barton]

I have an account number field where the data is formatted as follows:

A-22-95

The first one (or two) characters are always letters. The next set of
characters are numbers. And the third set represents a year. On a report,
I need my account numbers sorted FIRST by the year (in descending order),
then by the letter in alphabetical order, then by the middle number in
ASCENDING order.

For example:

A-21-98
B-22-98
C-40-98
A-20-97
B-19-97

I think I know what you are going to tell me - I have to break up the acct
numbers into 3 separate fields... but is there another way? Or do you have
any tips for handling it?

Well, three fields is the right way to do it so, of course
that's the recommended answer.

In this case, parsing the combined value may(?) not be too
bad:

set the first Sorting and Grouping level to:
=Val(Right(account, 2)
second level:
=Left(account, Instr(account, "-") - 1)
third:
=Val(Mid(account, Instr(account, "-") + 1))

 

[Pendragon]

I forgot the reference in the instr() function, so it should be:

instr([Account Number], "-")

You can keep your account numbers as they are. In the source query for the
report, add three fields at the beginning of the field list. Put these in
the field boxes:

SortYear: right$([Account Number],2)
SortBy: Descending

SortOther: left$([Account Number], len([Account Number]) - 3)
SortBy: Ascending

Since both your letter sort and numeric (non-year) sort are ascending, the
one field will sort appropriately - AA-25 will list before CG-56, etc., and A
will list before AA, etc.

If for some reason you need to break up the SortOther, then

SortLetter: left$([Account Number],len(instr([Account Number])) - 1)
SortBy: Ascending

SortNumber: mid$([Account Number], instr([Account Number]) + 1, 2)
Sort By: Ascending

The len() function allows the consideration of either 1 or 2 places for a
letter (A or AA). The SortNumber assumes that your number in the middle of
the account number will always be 2 digits.

In design view of your report, set your Sorting & Grouping accordingly. It
is not necessary to have the Header or Footer for each grouping, nor is it
necessary to have textboxes or fields on your report to represent the sorting
data.

Have fun!

I have an account number field where the data is formatted as follows:

A-22-95

The first one (or two) characters are always letters. The next set of
characters are numbers. And the third set represents a year. On a report,
I need my account numbers sorted FIRST by the year (in descending order),
then by the letter in alphabetical order, then by the middle number in
ASCENDING order.

For example:

A-21-98
B-22-98
C-40-98
A-20-97
B-19-97

I think I know what you are going to tell me - I have to break up the acct
numbers into 3 separate fields... but is there another way? Or do you have
any tips for handling it?

 

[Sandie]

Thanks. In theory, both solutions would work pretty well. The only problem
is that the two digit year values don't get sorted properly b/c the way
Access sees it, 01 definitely comes before 98 (even though to ME 01 = 2001
and 98 = 1998).

So I think I will have to go with separating the numbers.

Thanks again!

 

[Pendragon]

You can convert the text to string and add change the year format to "yyyy".

Expr1: IIf(Val([insert the SortYear extraction as I noted above])<=99 And
Val([same insert])>=50, "19" & [same insert], "20" & [same insert])

This will convert 01 to 2001 and 98 to 1998. Sorting on this field will
sort as though it were a true Year. If you need this to be treated as a
number, then enclose the entire IIF statement in Val(). If you only need to
use this field to sort, you can opt not to display the field and simply use
the two character extraction.

I didn't know that you could write statements into the sorting and grouping
like you could the RecordSource or DataSource in a Report Property -
obviously since Marshall has given you that option, it's a reality! So you
most likely could use in the Report Sorting & Grouping what I gave you above
except use his Val() statement, i.e.,

=IIf(Val(Right(account,2))<=99 And Val(Right(account,2))>=50, "19" &
Right(account,2), "20" & Right(account,2))

Note that I had to put a lower bound on the year (1950) - change that to
however far back you need to go.

 

[Marshall Barton]

Access (Windows?) uses 30 as the lower bound for rwo digit
years in the 20th century.

Ever since the Y2K problem became a serious issue, two digit
years have been a no-no. Continuing to use them (with the
lower bound) is just creating a Y2030 problem.
--
Marsh
MVP [MS Access]

You can convert the text to string and add change the year format to "yyyy".

Expr1: IIf(Val([insert the SortYear extraction as I noted above])<=99 And
Val([same insert])>=50, "19" & [same insert], "20" & [same insert])

This will convert 01 to 2001 and 98 to 1998. Sorting on this field will
sort as though it were a true Year. If you need this to be treated as a
number, then enclose the entire IIF statement in Val(). If you only need to
use this field to sort, you can opt not to display the field and simply use
the two character extraction.

I didn't know that you could write statements into the sorting and grouping
like you could the RecordSource or DataSource in a Report Property -
obviously since Marshall has given you that option, it's a reality! So you
most likely could use in the Report Sorting & Grouping what I gave you above
except use his Val() statement, i.e.,

=IIf(Val(Right(account,2))<=99 And Val(Right(account,2))>=50, "19" &
Right(account,2), "20" & Right(account,2))

Note that I had to put a lower bound on the year (1950) - change that to
however far back you need to go.

Thanks. In theory, both solutions would work pretty well. The only problem
is that the two digit year values don't get sorted properly b/c the way
Access sees it, 01 definitely comes before 98 (even though to ME 01 = 2001
and 98 = 1998).

So I think I will have to go with separating the numbers.

Thanks again!

 

[Pendragon]

Cool - so you wouldn't need the ">=50" portion, correct?

Is your statement an omen of programming havoc to come, or simply a
self-induced mess should someone continue to use two-digit years?

I don't, btw.

Access (Windows?) uses 30 as the lower bound for rwo digit
years in the 20th century.

Ever since the Y2K problem became a serious issue, two digit
years have been a no-no. Continuing to use them (with the
lower bound) is just creating a Y2030 problem.
--
Marsh
MVP [MS Access]

You can convert the text to string and add change the year format to "yyyy".

Expr1: IIf(Val([insert the SortYear extraction as I noted above])<=99 And
Val([same insert])>=50, "19" & [same insert], "20" & [same insert])

This will convert 01 to 2001 and 98 to 1998. Sorting on this field will
sort as though it were a true Year. If you need this to be treated as a
number, then enclose the entire IIF statement in Val(). If you only need to
use this field to sort, you can opt not to display the field and simply use
the two character extraction.

I didn't know that you could write statements into the sorting and grouping
like you could the RecordSource or DataSource in a Report Property -
obviously since Marshall has given you that option, it's a reality! So you
most likely could use in the Report Sorting & Grouping what I gave you above
except use his Val() statement, i.e.,

=IIf(Val(Right(account,2))<=99 And Val(Right(account,2))>=50, "19" &
Right(account,2), "20" & Right(account,2))

Note that I had to put a lower bound on the year (1950) - change that to
however far back you need to go.

Thanks. In theory, both solutions would work pretty well. The only problem
is that the two digit year values don't get sorted properly b/c the way
Access sees it, 01 definitely comes before 98 (even though to ME 01 = 2001
and 98 = 1998).

So I think I will have to go with separating the numbers.

Thanks again!


:

Sandie wrote:

I have an account number field where the data is formatted as follows:

A-22-95

The first one (or two) characters are always letters. The next set of
characters are numbers. And the third set represents a year. On a report,
I need my account numbers sorted FIRST by the year (in descending order),
then by the letter in alphabetical order, then by the middle number in
ASCENDING order.

For example:

A-21-98
B-22-98
C-40-98
A-20-97
B-19-97

I think I know what you are going to tell me - I have to break up the acct
numbers into 3 separate fields... but is there another way? Or do you have
any tips for handling it?


Well, three fields is the right way to do it so, of course
that's the recommended answer.

In this case, parsing the combined value may(?) not be too
bad:

set the first Sorting and Grouping level to:
=Val(Right(account, 2)
second level:
=Left(account, Instr(account, "-") - 1)
third:
=Val(Mid(account, Instr(account, "-") + 1))

 

[Marshall Barton]

No, you still need the lower bound because you are supplying
the century. I was just suggesting that you use 30 for the
sake of being consistent with what Access does.

It's an omen of self-induced programming havoc for people
like Sandie (or their company) that never did convert to
four digit years. She really should bite the bullet and
split that compound field into separate fields with a full
year value in an integer field.

Old war story for those that don't think their program will
still be around in 20+ years. Back in '65-'66, I wrote a
compiler for several thousand programmers to use to write
their programs. THIRTYTWO YEARS later (9 years after I
retired), I get a call that all those programs were still
being used, but they couldn't find a computer that could run
my compiler and could I please rewrite it to run on a modern
machine. After slowly counting to 10, I said sure - my rate
is $100/hr and they will have to provide a team of 8 top
notch programmers for 2 years. Thank goodness, they did not
call back.
--
Marsh
MVP [MS Access]

Cool - so you wouldn't need the ">=50" portion, correct?

Is your statement an omen of programming havoc to come, or simply a
self-induced mess should someone continue to use two-digit years?

I don't, btw.

Access (Windows?) uses 30 as the lower bound for rwo digit
years in the 20th century.

Ever since the Y2K problem became a serious issue, two digit
years have been a no-no. Continuing to use them (with the
lower bound) is just creating a Y2030 problem.

You can convert the text to string and add change the year format to "yyyy".

Expr1: IIf(Val([insert the SortYear extraction as I noted above])<=99 And
Val([same insert])>=50, "19" & [same insert], "20" & [same insert])

This will convert 01 to 2001 and 98 to 1998. Sorting on this field will
sort as though it were a true Year. If you need this to be treated as a
number, then enclose the entire IIF statement in Val(). If you only need to
use this field to sort, you can opt not to display the field and simply use
the two character extraction.

I didn't know that you could write statements into the sorting and grouping
like you could the RecordSource or DataSource in a Report Property -
obviously since Marshall has given you that option, it's a reality! So you
most likely could use in the Report Sorting & Grouping what I gave you above
except use his Val() statement, i.e.,

=IIf(Val(Right(account,2))<=99 And Val(Right(account,2))>=50, "19" &
Right(account,2), "20" & Right(account,2))

Note that I had to put a lower bound on the year (1950) - change that to
however far back you need to go.

:

Thanks. In theory, both solutions would work pretty well. The only problem
is that the two digit year values don't get sorted properly b/c the way
Access sees it, 01 definitely comes before 98 (even though to ME 01 = 2001
and 98 = 1998).

So I think I will have to go with separating the numbers.

Thanks again!


:

Sandie wrote:

I have an account number field where the data is formatted as follows:

A-22-95

The first one (or two) characters are always letters. The next set of
characters are numbers. And the third set represents a year. On a report,
I need my account numbers sorted FIRST by the year (in descending order),
then by the letter in alphabetical order, then by the middle number in
ASCENDING order.

For example:

A-21-98
B-22-98
C-40-98
A-20-97
B-19-97

I think I know what you are going to tell me - I have to break up the acct
numbers into 3 separate fields... but is there another way? Or do you have
any tips for handling it?


Well, three fields is the right way to do it so, of course
that's the recommended answer.

In this case, parsing the combined value may(?) not be too
bad:

set the first Sorting and Grouping level to:
=Val(Right(account, 2)
second level:
=Left(account, Instr(account, "-") - 1)
third:
=Val(Mid(account, Instr(account, "-") + 1))