displaying query and report in order by day and month

[Jon M.]

I have a query that I want to run a report from, my query uses month and day
as separate fields as is the year, for example January is just that January,
and the days are 1,2,3,etc.. this is so the user can use a drop down menu
from the form to select the date. When i run my query by year it lists the
months out of chronological order: February first then January, as opposed to
the other way around. When I run by specific days in a month it displays the
days out of order as well, regardless of trying to sort by ascending or
descending. My dates would go in this type of order: 1,14,15,2,3,etc... I
am looking for some help on how I can order these fields to display on my
report in chronological and numerical order. Any help is always appreciated!!

 

[Jerry Whittle]

First thing is to do any sorting in the report. Sorts in a query used as the
record source for a report are disregarded and resorted in the report.

I'm assuming that the day, month, and year are stored in different fields in
the table.

Your day field is probably a text field and not a number data type.
Therefore "11" sorts before "2". You could try to change it from Text to
Number and see if there are any problems. Do this on a copy of the database
first in case it messes up things.

You could also use the CInt function. Something like:
TheDay: CInt([Day])

Since you have the Month field as Text also and the data is written out like
"JUNE", that presents a bigger problem. You may need another table called
something like Months with two columns:
MonthName MonthNumber
January 1
February 2
ect.

Then you could link the two tables together and sort on the MonthNumber
field. Of course if someone mispelled February as Feburary, there will be
problems.

The main problem is that you have the dates split up in three different
fields. That data should all be in one Date/Time field. Access has many very
good date and time functions which an handily manipulate dates like Date(),
Month(), DatePart, DateSerial to name just a few. You wouldn't have to jump
through all the hoops listed above if things were in a real date/time field.

 

[Wayne-I-M]

You have (it seems) lots of field with text in.

You only need one field (date/time).

Sort the query on this

For your reports - search help on
Extract a part of a date or time value
and also on
DatePart

Sort your report on these.

Good luck