Report Grouping - data is dd/mm/yyyy, boss just wants month!

[Johnrg]

Guys, Gals,

I have a report that I run from a command button on a form.

The record source for this form(subform) is a query.

My data in the backend table and the query are formatted as dd/mm/yyyy, which is how we want it to stay.

Problem is my boss wants the records in his report shown as "month" and also grouped into "month".

So, as an example, records that have dates like 25/09/2009, 01/09/2009 and 15/09/2009 will show on the report as "September" and be grouped togeather as "September"

Can someone let me know how to get my report to format its "DateRequired" feild into month rather than day?

Thanks do much.

JohnG

 

[shadow9449]

John:

The easiest way to do this is:

- Create a group on your report. In the grouping definition, set the Group Interval to Month

- In the Header for your group, put a text box with the control source set to something like =Format$([my_date],"mmmm yyyy",0,0). Replace my_date with whatever your date field is actually called and you can tweak the formatting of the field to display as you like. The way I have it will show as 'September 2009'

I don't know what your Access experience is but if it's minimal, your best bet is to accomplish this with a Report wizard. If it's moderate then it should be straightforward to follow the direction I provided. Feel free to ask if you don't understand. Everything I've mentioned is documented sometimes in the Access help.

Good luck

SHADOW

 

[Johnrg]

Hi Shadow,
Thanks for your email.
I am a bit confused to be honest.
The report is already created and the feild I want to format and group on is called "Required"
I have set the grouping option for this feild to "yes" for this feild but when I tried to change this feilds group interval to "month" but it threw up an error message saying it had to be numerical.
Where exactly do I put the format code? I presumed in the "OnFormat" property box of this header but it does not seem to work.
Can you let me know where I am going wrong.
You say create a group called month? How do I create a group on something that does not exist - there is no feild called month?
Thanks
JohnG

 

[stopher]

I would create the month expression in the underlying query i.e. just create a new field in your query called, say, txtMonth with the following formula (similar to the one already suggested by shadow):

Format([mydate],"mmmm")

Then when you go into the report design, this field will be available to you to group etc.

I would avoid naming a field as "month" as month is a reserved word.

hth
Chris

 

[shadow9449]

Hi Shadow,
Thanks for your email.
I am a bit confused to be honest.
The report is already created and the feild I want to format and group on is called "Required"
I have set the grouping option for this feild to "yes" for this feild but when I tried to change this feilds group interval to "month" but it threw up an error message saying it had to be numerical.
Where exactly do I put the format code? I presumed in the "OnFormat" property box of this header but it does not seem to work.
Can you let me know where I am going wrong.
You say create a group called month? How do I create a group on something that does not exist - there is no feild called month?
Thanks
JohnG

- I did not say to call your field month, I said to set the group definition interval to Month

- if your field is called 'required' then use 'required' where I said "my_date".

- do you know how to create groupings on a report? there is a button on the report design toolbar that allows you to do so. You can create headers to each section. That's where you would set the interval to Month. Not one line of code is required here.

- Lastly, my suggestion was to put a text box in the Required group header with the Format$([required],"mmmm yyyy",0,0) as the control source.

If you are not familiar with the concept of groups on reports, I recommend that you read up on this topic. Once you do so, this will become perfectly clear. As well, you'll be able to create far more effective reports using grouping.

SHADOW

 

[shadow9449]

RAQ Report can meet your needs. I'm using RAQ Report. RAQ Report is a free excel-like reporting tool. You can get free RAQ Report at http://www.raqsoft.com/.

Spam reported

SHADOW

 

[Rich]

Format([mydate],"mmmm")

The Report will not sort correctly, using that format