access database fitler criteria

[Guest]

I'm currently working in a form view for a engineering drawing database. I
want to sort the database by two different fields. So I'm using the advanced
filter/sort option on the records drop down menu.

The filter field I want to sort by is the drawing number. I sort that in
ascending order and it works fine.

My problem comes when I try to sort the date field. The dates are entered
in the following manner: mm-dd-yyyy. When I sort them in descending or
ascending order is when I run into trouble. It sorts by the month and
doesn't do any further sorting on the field. It will start with a 12 for
december when sorting and then it will continue thru the months 12,11,10,
etc. However, it doesn't sort by the day or year. I will have a year of
1997 show up before the year 2000. What am I missing in getting around this
so that it sorts properly.

Any help would be greatly appreciated we're constantly sending out old
drawings to the shop floor.

Please let me know if you require any more information to help me with this
issue

 

[John W. Vinson]

I'm currently working in a form view for a engineering drawing database. I
want to sort the database by two different fields. So I'm using the advanced
filter/sort option on the records drop down menu.

The filter field I want to sort by is the drawing number. I sort that in
ascending order and it works fine.

My problem comes when I try to sort the date field. The dates are entered
in the following manner: mm-dd-yyyy. When I sort them in descending or
ascending order is when I run into trouble. It sorts by the month and
doesn't do any further sorting on the field. It will start with a 12 for
december when sorting and then it will continue thru the months 12,11,10,
etc. However, it doesn't sort by the day or year. I will have a year of
1997 show up before the year 2000. What am I missing in getting around this
so that it sorts properly.

Any help would be greatly appreciated we're constantly sending out old
drawings to the shop floor.

Please let me know if you require any more information to help me with this
issue

Well... let's clear up some jargon here.

In Access, a "Database" is the .mdb file containing multiple Tables, Forms,
Reports, code and other objects. You can't "sort a database" - you can sort
the data in a Table by using a Query.

A Table also cannot be sorted, as a table. If you're opening the table
datasheet ... don't. That's not what tables are for! Instead create a Query
sorting the table as desired, and base a Form on that Query. Use the Form to
interact with the data. The Query can have "parameters" - e.g. you can use the
query to select only by a specific drawing number, a range of dates, etc.

The sorting problem with the date is probably because you're storing the date
in a field of Text datatype. It's sorting (correctly!!!) as a text string -
the text string "12-15-1997" is, alphabetically, before the text string
"2-10-2007". This can be resolved by storing your dates in a Date/Time field.
Date/Time values are stored as a number (a count of days since an arbitrary
start point) and sort chronologically; you can use the Format property of a
textbox to display the date in any number of different formats.

John W. Vinson [MVP]