Ascending Order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field to store the term/year of the courses each student is supposed
to take. It looks something like this: 1/2000. I have a list of such terms
but the ascending order I've set turn out like this:

1/2000
1/2001
1/2002
2/2000
2/2001
2/2002
3/2000
3/2001
3/2002

What I need is this:

1/2000
2/2000
3/2000
1/2001
2/2001
3/2001
1/2002
2/2002
3/2002

What's the trick in getting it sorted out like this? Thanks.
ck
 
If you are storing more than one fact in a single field, you are "violating"
one of the principals of relational design. You are storing "term" and
"year" in your field... Consider using two fields, which would make your
sorting (and future queries) much easier.

One way to do what you've asked about is to use the Left() and Right()
functions in a query to create, on the fly, the two "fields" of term and
year, then sort on those.
 
CK,
You didn't indicate what type of field you have... Text or Date?
I'm assuming Text... because Date wouldn't have this problem.

In a query behind a form or report, you could create a couple of
calculated fields to force proper sorting.
Add these fields to your query... (use your names)
SortYear : Val(Right([TermYear],4))
and
SortMonth : Val(Left([TermYear],1))
and sort both Ascending.

That will force your Text field to sort properly.
hth
Al Camp
 
CK said:
I have a field to store the term/year of the courses each student is supposed
to take. It looks something like this: 1/2000. I have a list of such terms
but the ascending order I've set turn out like this:
Click to expand...

Your field values look enough like dates that something like

SortBy: CDate([TermField])

should work.

-Greg.
 
Thanks for all your suggestions. Took me a little while to figure out
AlCamp's idea and I got it working. Phew! Thanks once again guys.
ck
 
Back
Top