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