Re: Sorting by complex alphanumeric data

Tech-Archive recommends: Fix windows errors by optimizing your registry

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]


Pendragon wrote:

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.

"Sandie" wrote:

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!


"Marshall Barton" wrote:

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))

--
Marsh
MVP [MS Access]


.

Relevant Pages

  • Re: [OT] stable algorithm with complexity O(n)
    ... I have to create stable algorithm for sorting n numbers from interval ... lower bound for sorting is O. ... To beat n * log_2 n just use a bucket sort: ... maximum you can sort them digit by digit for O, ...
    (comp.lang.python)
  • Re: [OT] stable algorithm with complexity O(n)
    ... Unless I grossly miss out on something in computer science 101, the lower bound for sorting is O. ... The lower bound for sorting where you make a two way branch at each step is O, but if you can choose between k possible orderings in a single comparison you can get O. ... To beat n * log_2 n just use a bucket sort: for numbers with a known maximum you can sort them digit by digit for O, and if you don't restrict yourself to decimal then k can be as large as you want, so for the problem in question I think you can set k=n and ==1 so you get O ...
    (comp.lang.python)
  • Re: Sorting by complex alphanumeric data
    ... you still need the lower bound because you are supplying ... It's an omen of self-induced programming havoc for people ... I didn't know that you could write statements into the sorting and grouping ... most likely could use in the Report Sorting & Grouping what I gave you above ...
    (microsoft.public.access.reports)
  • Re: Sorting algorithm when comparison is heavy
    ... asking about sorting things with very expensive compare operations. ... single digit of the long value. ... sorted pile after handling the most significant digit. ...
    (comp.programming)
  • Re: On the complexity of determining whether n numbers are distinct
    ... this can be shown by reducing the problem to one of sorting but the ... bounds for algebraic computation trees" by Michael Ben-Or proves the n ... log n lower bound in the algebraic decision tree model of computation. ...
    (comp.theory)