Reports- Use same field several times with a filter?

[Captain Smith]

Hi. I've seen similar posts, but none of them seem to pertain to this.

What is the easiest way to do the following?
I have a query that has a month field (1,2,3, up to 12). I would like to generate a Report from this query that will allow me to list data from January, February, and March on the same report. I would like to use the same month field three times on the report, and just filter each one for the month I am after in that particular area of the report. Is this at all possible with a Report? Do I have to use to a Form? If so, do I need to make three different Forms to do this, or can I do it one Form? I could make three queries rolled up into a final query to generate the Report, but this would be very awkward due to the complexity of the initial query.

Also, once a Report is made based on a query, how can I add additional fields from that same query?

Thanks very much.

 

[SydneyGeek]

If you want
Jan
Record
Record
etc

Feb
Record
Record
etc

March
Record
Record
etc

There's an easier way to do this than the one you propose. If you HAVEN'T already built the report, use the Wizard. When you have the option to group records, group by Month No. The other fields go in Detail.

If you HAVE already built the report, go to Design view. View | Sorting and Grouping and select Month No. You will need to put it at the top of the list. Still in that dialog, at the bottom, change Group Header to Yes. That will give you a section in the report where you can put the month No field. If it's already in the report, relocate it.

So far, so good. To display the month name instead of the month number, you'll need another textbox. Drag one onto the report from the Toolbox. Right-click its Properties, and insert this expression in the Control Source --
=Choose([MonthNo],"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
You can use the full month names if you want. Make sure you get the field name right

This should give you a report grouped on all the months -- try it and see. To filter for just the first three months, go to the query that underlies the report. In Design view for the query, find the Month No field and, in the Criteria row, enter <=3

See you you go with that
Denis

 

[Norie]

Is the MonthNo field based on a date?

If so a way to get the full text for the month would be to format that date as a month.

eg

Original expression for MonthNo could be something like this

MonthNo:Month([SomeDateField])

You could then use Denis's ideas to do what you want.

to get actual month use

MonthName:Format([SomeDateField], "mmmm")

 

[Captain Smith]

Thanks very much, both of you. I will check out what you posted.