sorting function yields unwanted results

[amjjam]

Hi,

I have a report titled, “Daily Communication Summary.†The Page Header
includes columns titled, “Comm Date by Month†and “Comm Date.†The data for
the Comm Date by Month is delivered as a Comm Date Header whose control
source is the expression =Format$([CommDate],â€mmmm yyyyâ€,0,0). In the actual
report it reads May 2009, for example. The control source for the Comm Date
is the actual date on which the communication took place. I’ve used the short
date format with an input mask of 99/99/0000;0;_. Output gets delivered in
the detail section of the report. The SQL query on which the report is based
includes the CommDate from the Communications table sorted in Ascending
order; so the Sorting and Grouping function shows ascending order for it too.

Here’s the problem. The report correctly shows the Communication Date by
month in ascending order: April 2009 is above May 2009, which is above June
2009 on the page reading from top to bottom. The actual communication dates
are listed in descending order with the latest communication listed first and
the oldest last for each person. So 5/22/2009 is above 5/18/2009, which is
above 5/3/2009, etc. It looks like this:

Comm Date By Month Comm Date
April 2009
4/12/2009
May 2009
5/22/2009
5/18/2009
5/3/2009
June 2009
6/22/2009
6/10/2009

I would like for the dates in the Comm Date column to be listed in
ascending order too. In other words, I want to read from top to bottom,
oldest to latest, 5/3/2009 to 5/18/2009 to 5/22/2009. It should look like
this:

Comm Date By Month Comm Date
April 2009
4/12/2009
May 2009
5/3/2009
5/18/2009
5/22/2009
June 2009
6/10/2009
6/22/2009

Can anyone tell me how to set the sort order for the CommDate to affect both
the Comm Date by Month and the Comm Date columns the same way? Thanks, amjjam

 

[Beetle]

I would do the CommDateByMonth field in the query;

CommDateByMonth: Format([CommDate], "mmmm yyyy")

Then in the report I would have one Sorting/Grouping level
based on CommDateByMonth with a header, and another
level based on CommDate.

 

[amjjam]

Hi,

Thanks for trying to help. Maybe I need some clarification at a more basic
level. I’m trying to run the report off of data in only one field from the
Communications table. The field is named CommDate. I want to generate 2
different “looks†for it in the report. The field gathers data in a short
date format like “6/23/2009.†I want to change that to read “June 2009†for
the Comm Date By Month column and keep it in that 6/23/2009 format for the
Comm Date column in the report. Then I want both columns in the report to
fill in their data in ascending order from oldest communications to newest as
one reads down the page. It sounds like you believe I need to create two
fields for the table so I can sort them separately. Do I understand
correctly? And is there no way to work around that? It sounds like I’d be
doing duplicate data collection. I’m fairly new to Access and have never had
this kind of sorting problem before, so thanks for your help and for being
patient while I learn. ~ amjjam

I would do the CommDateByMonth field in the query;

CommDateByMonth: Format([CommDate], "mmmm yyyy")

Then in the report I would have one Sorting/Grouping level
based on CommDateByMonth with a header, and another
level based on CommDate.
--
_________

Sean Bailey

Hi,

I have a report titled, “Daily Communication Summary.†The Page Header
includes columns titled, “Comm Date by Month†and “Comm Date.†The data for
the Comm Date by Month is delivered as a Comm Date Header whose control
source is the expression =Format$([CommDate],â€mmmm yyyyâ€,0,0). In the actual
report it reads May 2009, for example. The control source for the Comm Date
is the actual date on which the communication took place. I’ve used the short
date format with an input mask of 99/99/0000;0;_. Output gets delivered in
the detail section of the report. The SQL query on which the report is based
includes the CommDate from the Communications table sorted in Ascending
order; so the Sorting and Grouping function shows ascending order for it too.

Here’s the problem. The report correctly shows the Communication Date by
month in ascending order: April 2009 is above May 2009, which is above June
2009 on the page reading from top to bottom. The actual communication dates
are listed in descending order with the latest communication listed first and
the oldest last for each person. So 5/22/2009 is above 5/18/2009, which is
above 5/3/2009, etc. It looks like this:

Comm Date By Month Comm Date
April 2009
4/12/2009
May 2009
5/22/2009
5/18/2009
5/3/2009
June 2009
6/22/2009
6/10/2009

I would like for the dates in the Comm Date column to be listed in
ascending order too. In other words, I want to read from top to bottom,
oldest to latest, 5/3/2009 to 5/18/2009 to 5/22/2009. It should look like
this:

Comm Date By Month Comm Date
April 2009
4/12/2009
May 2009
5/3/2009
5/18/2009
5/22/2009
June 2009
6/10/2009
6/22/2009

Can anyone tell me how to set the sort order for the CommDate to affect both
the Comm Date by Month and the Comm Date columns the same way? Thanks, amjjam

 

[John W. Vinson]

Thanks for trying to help. Maybe I need some clarification at a more basic
level. I’m trying to run the report off of data in only one field from the
Communications table. The field is named CommDate. I want to generate 2
different “looks” for it in the report. The field gathers data in a short
date format like “6/23/2009.” I want to change that to read “June 2009” for
the Comm Date By Month column and keep it in that 6/23/2009 format for the
Comm Date column in the report. Then I want both columns in the report to
fill in their data in ascending order from oldest communications to newest as
one reads down the page. It sounds like you believe I need to create two
fields for the table so I can sort them separately. Do I understand
correctly? And is there no way to work around that? It sounds like I’d be
doing duplicate data collection. I’m fairly new to Access and have never had
this kind of sorting problem before, so thanks for your help and for being
patient while I learn. ~ amjjam

Don't confuse data FORMATTING with data CONTENT!

Your report's Sorting and Grouping should sort the data by CommDate... *not*
by any Format expression, which will sort alphabetically (April, August, etc.)
Just set the Format property of the textbox on the form to mmmm yyyy rather
than using the Format() function in your query.

 

[amjjam]

Hi John,

As per your advice, I have now 1) cleared the “Sort†box in the report’s SQL
query as it’s redundant and overridden by the sorting/grouping function; 2)
changed the control source property of the “Comm Date By Month†to CommDate
so it takes data from the communications table; 3) placed mmmm yyyy in the
format property for the “Comm Date By Monthâ€; and 4) set the sorting and
grouping for the CommDate field/expression to ascending. While the month and
year are running in ascending order, the actual daily dates are still running
in descending order for each month. I think the problem may lie in the group
properties of the sorting and grouping. I want the data staggered with the
month and year on the line above the first communiqué listed, like this:

Comm Date By Month Comm Date
April 2009
4/12/2009
May 2009
5/3/2009
5/18/2009
5/22/2009

The sorting and grouping properties are thus set to:
Group Header – yes
Group Footer – no
Group on – Month
Group interval – 1
Keep together – no

I figured that since the Group Header property would govern and, in essence,
rule over the detail data the CommDateHeader showing the month and year of
the Comm Date By Month in ascending order is overruling any sorting function
on the Detail data listing the actual communication date. When I tried
eliminating the Header and putting the month and date on the same line with
the first communication date to get the ascending results I want, it still
returned the Comm Date in descending order like this:

Comm Date By Month Comm Date
April 2009 4/12/2009
May 2009 5/22/2009
May 2009 5/18/2009
May 2009 5/3/2009

I don’t know what else to try. This latter attempt looks messy and isn’t
what I want. Any further help would be really appreciated! Thanks, amjjam

 

[Pieter Wijnen]

with the latter approach you could use the 'hide duplicates' control
property for the 'month' column

hth

pieter

 

[amjjam]

Hi,

I got the problem resolved. In the sorting grouping function I had to list
"CommDate … Ascending" twice. First with a group header property set to yes
and then with a group header property set to no. Thanks for your help. ~
amjjam

BTW, Pieter’s use of the hide duplicates control does work, but isn’t
necessary when I use sorting/grouping twice.