Sorting by complex alphanumeric data

Discussion in 'Microsoft Access Reports' started by Guest, Jan 25, 2007.

  1. Guest

    Guest Guest

    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?
     
    Guest, Jan 25, 2007
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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!

    "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?
    >
     
    Guest, Jan 25, 2007
    #2
    1. Advertisements

  3. 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]
     
    Marshall Barton, Jan 26, 2007
    #3
  4. Guest

    Guest Guest

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

    instr([Account Number], "-")

    "Pendragon" wrote:

    > 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!
    >
    > "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?
    > >
     
    Guest, Jan 26, 2007
    #4
  5. Guest

    Guest Guest

    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]
    >
     
    Guest, Feb 5, 2007
    #5
  6. Guest

    Guest Guest

    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]
    > >
     
    Guest, Feb 5, 2007
    #6
  7. 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]
    >> >
     
    Marshall Barton, Feb 5, 2007
    #7
  8. Guest

    Guest Guest

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

    "Marshall Barton" wrote:

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

    >
    >
     
    Guest, Feb 5, 2007
    #8
  9. 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]


    Pendragon wrote:
    >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. :)
    >
    >"Marshall Barton" wrote:
    >
    >> 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.
    >>
    >>
    >> 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]
    >> >> >

    >>
    >>
     
    Marshall Barton, Feb 6, 2007
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Alphanumeric Sorting

    Guest, Dec 10, 2004, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    191
    Guest
    Dec 11, 2004
  2. Terry Lo via AccessMonster.com

    Sorting Report Field- Alphanumeric

    Terry Lo via AccessMonster.com, Jun 22, 2005, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    189
    Duane Hookom
    Jun 22, 2005
  3. Alex Martinez

    Sorting/Multiply Sorting

    Alex Martinez, Sep 18, 2005, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    163
    Marshall Barton
    Sep 18, 2005
  4. cna48

    Complex Sorting

    cna48, Jan 16, 2009, in forum: Microsoft Access Reports
    Replies:
    2
    Views:
    161
    Marshall Barton
    Jan 16, 2009
  5. Rab

    Counting alphanumeric data

    Rab, Jul 31, 2009, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    233
    KARL DEWEY
    Aug 5, 2009
Loading...

Share This Page