Goup header issue 1

[medic133]

My goal for a group header on a report is to have a header for each month of the year based on a date field within the header. Example:

January
4th
6th
13th

Febuary
5th
23rd

...ect.
(Both the date field and the unbound textbox are in the group header, but the date field is set to .visible=no)

I really think I need this unbound textbox in some way referencing the date field within the header so that if no dates for a given month appear in the underlying query, then the month won't appear on the report. Here is how I've gone about doing this:

Within the query I have created a field with the expression Format((date),"mm&quot where date is my date field with the format mm/dd/yyyy. This query returns the desired values (01, 02, 03, etc). I then have code for the report in the detail_format section as follows:

If me.text1="01" then
me.text2="January"
ElseIf me.text1="02" then
me.text2="February"
...etc. (where text1 controlsource is the expression from the query and text2 is the unbound textbox).

Believe it or not this works fine, only the appropriate values on the report are one group header off. For instance, if the date field in the first group is January, then there is text2 is blank. Regardless of what the next groups date field is, January shows up in text2. It's as if all of the text2 fields are one group level behind. Any suggestions on why this is happening? Also, if there is a way of calculating this within my query so that I can simply drag this calculated field into my report, I would like to know that too. If at all possible, I would like information or ideas regarding both of these potential answers as this may help my avoid problems in the future with grouping on a report. Thanks in advance!!

 

[dhookom]

Just use your date field in the group header and set the format to whatever you want to display. In your case, it should be:
Format: mmmm

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[medic133]

Duane,

Thanks for the advice. It worked like a charm!! Now I have another problem with this report. Using your suggestion, I was able to format an expression in the query my report is based on to give me exactly what I want which is a little different than what I initially wrote. Because the query has two parameters, a beginning and ending date than can span any number of years, I wanted the date to be in the form March '03, December '03, January '04, etc. The format in the query is as follows:

expr1: Format([date],"mmmm"&" '"&"yy&quot where date is a date field from a table.

From here, in the report I group on expr1. When I include the sort order as either ascending or descending, the report doesn't recognize this as a date field (I guess) so that the order is something like the following:

December '03
January '04
March '03

As you can see, these are ordered alphabetically rather than chronologically. Is there something I can do to get the order to go chronologically like the following:

March '03
December '03
January '04

Any further assistance would be greatly appreciated. Thanks again for your original answer!!

 

[dhookom]

There is nothing that states you have to sort/group on a field that you can see. Create a column in your query
YrMth:Format([Date],"yyyymm&quot
Then you can group by YrMth.

BTW: find a good naming convention and stick with it. Try to avoid using reserved words such as Date for field names. Also, don't accept the default "Expr1". Change it to something that is a visible indication of the value.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[medic133]

Duane,

Works absolutely perfectly!! The ideas regarding formatting and the fact that you're not restricted to visible fields have a variety of applications. I knew this, but being reminded of different situations in which this could be useful is well worth a star! Thanks for the help and the quick response!