Showing month of the year in chronological order in a report.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 15 Nov 2007 20:10 GMT

Hi everyone,

I am having a bit of a problem. I am doing a report to show monthly savings.
My report is based on a query with the following fields: Date closed by month
and Summary of savings. Here is the problem. The report shows the date closed
by month with the sum for each month. However, the month are not in
chronoligical order as I want them to be. Secondly, there is a random date of
December, 1899 in the report.

How do I get rid of the December 1899 date and how do I make the month list
in chronoligical order?

Thanks for your help.

Reply to this Message

Jerry Whittle - 15 Nov 2007 21:38 GMT

The Month function will return the number of the month. For example:

Debug.Print Month(#November 15, 2007#) returns 11

If you sort on the date field using the Month function, it should sort
correctly.

As for the centuries-old dates, Access store midnight on the morning of
December 30, 1899 as 0. December 3, 1899 is stored as 1.

Debug.Print CDBL(#December 30, 1899#) returns 0

also

Debug.Print Format(CDate(0),"Long Date") returns Saturday, December 30, 1899

I'm thinking that there is a 0 or 1 getting into the date text box of that
report somehow.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi everyone,
>
[quoted text clipped - 9 lines]
>
> Thanks for your help.

Reply to this Message

KARL DEWEY - 15 Nov 2007 21:41 GMT

>>How do I get rid of the December 1899 date

That date means that 0 (zero) is stored in the field. You can update to
Null but if you have criteria on that field you may need to revise queries.
Alternatively you can use an IIF statement in the report textbox to display
Null if that date.

>>how do I make the month list in chronoligical order?

Add a calculated fields and use in the Grouping and Sorting.
The calculated field would be like this ---
Year_Mon: Format([YourDateField], "yyyymm")

Signature

KARL DEWEY
Build a little - Test a little

> Hi everyone,
>
[quoted text clipped - 9 lines]
>
> Thanks for your help.

Reply to this Message