Sorting Months in report NOT alphabetically, but logically

Z

zaskodnik

I have a report that lists months and then details for each month
Problem is that it prints them in this order: April June May, not Apri
May June. How do I make it sort it according to time, no
aplhabetically
 
N

Nikos Yannacopoulos

Use one more month control in the same section of your report, formatted as
"yyyy/mm", and sort on that one. The control can be set to Visible > No, so
it doesn't dispaly or print.

HTH,
Nikos
 
Z

zaskodnik

I tried to add another month control.... I set "visible" to "no" an
made that header sorted by that control.
Now instead of doing this:
April
456
323
5656
May
455
789
654
June
3
466
78

It does this:
April
456
April
323
5656
May
455
789
May
654
June
3
June
466
78

... so it writes the month more than once...
any other suggestion?
Thanks
 
Z

zaskodnik

I tried to add another month control.... I set "visible" to "no" an
made that header sorted by that control.
Now instead of doing this:
April
456
323
5656
May
455
789
654
June
3
466
78

It does this:
April
456
April
323
5656
May
455
789
May
654
June
3
June
466
78

... so it writes the month more than once...
any other suggestion?
Thanks
 
Z

zaskodnik

The query that I use for this report has in each line an info abou
shipment. These are the columns on the query:
product name
amount
price
ext. price
month of shipment (says april, or May....) ... I use this as visible
date of shipment (I use it for sorting, but not visible)

When I do what you told me to do, it prints the "Month of Shipment" fo
EVERY DAY of shipment.

For example:
If I shipped in April:
10 pcs of apples on April 5th
20 pcs of pears on april 5th
30 pcs of cherries on April 10th

.... the report will be like this:
April
10 pcs if apples
20 pcs of pears
April
30 pcs of cherries

... April is printed twice

But I want the word "April" to be in report only once.

Hope this will provide you enough info to be able to answer m
question.
Thanks
 
Z

zaskodnik

The query that I use for this report has in each line an info abou
shipment. These are the columns on the query:
product name
amount
price
ext. price
month of shipment (says april, or May....) ... I use this as visible
date of shipment (I use it for sorting, but not visible)

When I do what you told me to do, it prints the "Month of Shipment" fo
EVERY DAY of shipment.

For example:
If I shipped in April:
10 pcs of apples on April 5th
20 pcs of pears on april 5th
30 pcs of cherries on April 10th

.... the report will be like this:
April
10 pcs if apples
20 pcs of pears
April
30 pcs of cherries

... April is printed twice

But I want the word "April" to be in report only once.

Hope this will provide you enough info to be able to answer m
question.
Thanks
 
M

Marshall Barton

zaskodnik said:
The query that I use for this report has in each line an info about
shipment. These are the columns on the query:
product name
amount
price
ext. price
month of shipment (says april, or May....) ... I use this as visible
date of shipment (I use it for sorting, but not visible)

When I do what you told me to do, it prints the "Month of Shipment" for
EVERY DAY of shipment.

For example:
If I shipped in April:
10 pcs of apples on April 5th
20 pcs of pears on april 5th
30 pcs of cherries on April 10th

... the report will be like this:
April
10 pcs if apples
20 pcs of pears
April
30 pcs of cherries

.. April is printed twice

But I want the word "April" to be in report only once.
Click to expand...


Are you saying that the month of shipment field is just the
name of the month and not an actual date? (How would you
distinguish shipments in the same month but in different
years?) If so, you need to group on an expression like:
=CDate([month of shipment] & " " & 2004)

Note that you can not sort/group on a control, it must be a
field or an expression of fields in the report record source
table/query.
 
Z

zaskodnik

No, it says for example "April 2004". Not only "April" I made a summar
query.... by months.

I also added one column with exact date... just for sorting. But when
used it for sorting (but not visible) in my report, it wrote "Apri
2004" every couple of lines... not only once
 
Z

zaskodnik

No, it says for example "April 2004". Not only "April" I made a summar
query.... by months.

I also added one column with exact date... just for sorting. But when
used it for sorting (but not visible) in my report, it wrote "Apri
2004" every couple of lines... not only once
 
M

Marshall Barton

zaskodnik said:
No, it says for example "April 2004". Not only "April" I made a summary
query.... by months.

I also added one column with exact date... just for sorting. But when I
used it for sorting (but not visible) in my report, it wrote "April
2004" every couple of lines... not only once.
Click to expand...


OK. "April 2004" is a valid date in a string (Access will
assume the first of the month). This means you can
group on the expression
=CDate([month of shipment])

Also, sort on the field [date of shipment] to get the
details in the exact order of shipment.

I don't know what kind of UI you're using to get this
information from the users, but the [month of shipment]
field is redundant and should be eliminated to prevent users
from making a mistake by entering inconsistent dates. If
you only have the [date of shipment] field, then you can
group on the expression
=DateSerial(Year([date of shipment],Month([date of
shipment],1)
 
Z

zaskodnik

Thank you all!

I fixed it.

I added to my query these 2 columns:

1. Month of ship: in the form yyyy/mm "2004/04"

and

2. Month of shipment: in the form "April 2004"

In the report I sort on the first, but view only second.

Thank you for your kindness and help.

Petr
 
Top