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

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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