SORT DATE IN ACCESS 2000

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

Guest

How do we sort data in Ms-Access 2000 for eg (A/1.... A/300). The number
system should be A/1, A/2, A/3........and so on.Instead the database sorts
the data as A/1, A/10, A/100 and so on.....

And also while sorting date also cant we sort it having all the months
together irrespective of the year. (eg. 1/4/2000, 5/4/2005, 2/3/2000) In this
example the date is sorted as 1/4/2000, 2/3/2000 and then 5/4/2005)
 
Part 1: Sorting A/1, etc. You first have to grasp the difference between
numbers and strings in access.
access will happly sort the numbers 1,2,3....1000 in the correct sequence.
If you store them as text it will sort them in the order:
1,10,11,...100,101,2,20,21, etc.
Therefore in the case at hand Access sees your data as text : A/1, A/2, etc.
and sorts the field happily in the "Correct order", but not the order you
want.


To get the order you want you can:

a. modify your design to have a Text field and a number field A|1, A|2
b. use a query to "pull apart the field" then sort it:
Select Left(FieldDate,2) as Alpha, Select
Clng(Right(FieldDate,Len(FieldDate)-2) as thisNumber
ORDERBy Alpha, Thisnumber
c. Modify your data to look like (recommended if and only if you can
absolutely assure that you know the limits of the 'numbers' -- if not you
WILL have to redo the whole thing):
A/0001, A/0010, A/0100, A/1000


Part 2: Sort dates by month not date: You would have to pull out the month,
then sort by that
Select Month(FieldDate) as thisMonth
Orderby thismonth, FieldDate

Ed Warren
 
Ed showed you the work-around but basically, IMHO, the problem caused by the
incorrect structure of your Table.

If the prefix "A" means something, you probably have other prefixes. In
this case you store 2 items of data in a Field which is not recommended in
database. Perhaps, you should split this into 2 Fields "Prefix" (Text) and
"SequenceNo" (Integer or Long).

This way, you can sort whichever you want.
 
Back
Top