sorting by sequential dates

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

Guest

i have a simple database for church contributors. the fields include the following info re contributions: date, name, cash, check#, amount. in developing the query for each contributor, i want to sort the date field in descending date sequential order. what do i need to do to establish a descending date sequential order sort by month?
 
If you are using the Query Builder add two column
1. Year([Date]
2. Month([Date]
The first field (Year([Date]) must precede the other. In the sort row select Descending for both. The year column must be used so that, say Sept 2003 will precede Sept 2002 and the intervening months are in the correct order.
HT
Terr
WOW! Are you a real goddess??

----- (e-mail address removed) wrote: ----

i have a simple database for church contributors. the fields include the following info re contributions: date, name, cash, check#, amount. in developing the query for each contributor, i want to sort the date field in descending date sequential order. what do i need to do to establish a descending date sequential order sort by month?
 
i have a simple database for church contributors. the fields include the following info re contributions: date, name, cash, check#, amount. in developing the query for each contributor, i want to sort the date field in descending date sequential order. what do i need to do to establish a descending date sequential order sort by month?
Click to expand...

Just put Descending on the date field to sort chronologically.

Could you explain what you mean, "by month"?
 
tnx, terry, for responding to my msg. i was truly stumped & needed to get on w/ it in order to meet a deadline. you were a godsend! shalom

indeed, i am a goddess. stay blessed!
 
the table from which i develop my queries has a column called "date." in it all the contributions on a particular sunday's dates are listed for all the contributors that date. the date changes every sunday w/ its attendant list of contributions/contributors.

when i develop queries, by name of contributor, every contribution for that person is listed in the simple query for the year 2003. when i sort by date using a descending or ascending sort, the list sorted by dates looks like this, for example:

(ascending sort)
1 aug 03
5 nov 03
13 aug 03
20 dec 03, etc.
31 january
this result is not what i'm trying to achieve. i would like to set up criteria that would list all the dates
for january through december 2003 in a sequential order for each month, i.e., all the january dates would be listed together in ascending or descending order, then february's dates, followed by march's, etc. until the end of december.

hope this clarifies things. tnx for your assistance.
 
Pardon me for jumping in.

Obviously this is sorting the date as if it were a text string. You could try
adding a calculated column based on the "date" field to do the sorting

Field: SortDate: DateValue([TableName].[Date])
Sort: Ascending

If your [Date] field has any invalid data in it, this will generate an error.
So you might want to try a bit more complicated expression for the calculated expression.

Field: SortDate: IIF(IsDate([TableName].[Date]),DateValue([TableName].[Date]),Null)

By the way, Date is a bad name for a field, because Access has a function
entitled "Date". Access (and you) can become confused as to which you are
referring. I would try EventDate or ActionDate or DonationDate or some other
name that specifies a bit more about the date itself.
 
Back
Top