Display only the Month of a Date Field

[nicolaasjan2005]

I have a query that selects data for a month (any given month) between the first date of the month and the last day of that same month. The return I get is exactly what I require.

Is there any way, on the report based on the query results, to display only the month that the query pertains to?

I've tried lots of ideas, but nothing works. Any suggestions?

Thanks, all!

 

[pbaldy]

Try

Format(DateField, "mmmm")

 

[nicolaasjan2005]

It doesn't work in the text box on the report.

On the query design I have the "between" statement in the date field.
So, if that's where it is to go, how do I put it in place, and where without losing the Between query?

I'm using access 2003 BTW.

 

[pbaldy]

There's no reason it shouldn't work in a textbox, though it would need an = preceding it:

=Format(DateField, "mmmm")

 

[nicolaasjan2005]

What I have in the report textbox is

=Format([DonDate],"mmmm")

The DonDate is the field in the query that hold the "between" criteria.

I tried to leave out the square brackets from around the datefield but access puts them back in.

The error I get is a circular reference. This is what it says....
"The Control Source property of the selected control is an expression that includes a reference to the control. Circular reference will cause the expression to fail."

 

[nicolaasjan2005]

OK, I've tried something different that appears to be working so far on the test data I'm using.

I've formatted the top row of the Date Field in the query to "DateField:Format$([tblDonations].[DateField],'mmmm yyyy') and in the Criteria I've called the prompt [Enter Full Month and Year:] and upon the running the query, after entering say, "April 2009", it provides me with the return I require (all the data recorded for April 2009).

Now in the report, I get the "month and year" in the date field as required.

I just don't understand the "circular reference" error I got earlier.

I'm assuming this new query will pick up all the data for the month and year requested - the test data seems to be working.

In theory, does anyone see any potential problems I may encounter?

Thanks for the help.

 

[pbaldy]

The error probably means that you had a control (textbox) with the same name as the field in the data. That often causes problems. The solution would have been to change the name of the control.

 

[michane]

There's no reason it shouldn't work in a textbox, though it would need an = preceding it:

=Format(DateField, "mmmm")

pbaldy, can I use the same format above as an expression under the "Group on", for my report?

 

[pr2-eugin]

pbaldy, can I use the same format above as an expression under the "Group on", for my report?

I am not pbaldy, but I am another Paul on this Forum..

I do not think why that would be a problem.. So, I would say Yes..