I need some help with this. I want to have an field of data on a report, this data field should always display data from the current month.

For example:

Let's say we are talking about this month (December) and the data I want displayed is the number of Active Accounts (which is stored on my table) for December.There's going to be other data, from past months on the report, as well. I want to have the current months data displayed though so it can be compared to that.

Does that make any sense?

Try making a subreport which is bound to a query that displays only the current month's data.
When inserting the subreport into your main report don't bind them together, that way the subreport will always display.
I assume you are comfortable with making a query to return results for the current month only. If not sing out.

hth

Mat

That's the part I'm having problems with is getting the current months data.....I'm not quite sure how to go about that.

Two Ways to Do it:

Set a Criteria in a Date Field in your Query as Between [Date1] and [Date2] Where they are the dates you need

or

Add a field to your query like so:

MONTH: month([DATEFIELD])

where datefield is the date you want to filter and sets its criteria to:

month(date())

which will filter to the current month (based on system date)

hth

Ben

Okay, so, In my query (this is a just a test query for now) I have two fields.... Date and Total Active Accounts.

I was trying to do something along the line of your second version, originally, but I couldn't figure out what to use for the criteria.

I tried just now putting "month(date())", but it returns no results. What am I doing wrong?

Ok, you will need 3 fields Your First two (Date and Total Active) then a 3rd field:

(Type this in manually into the 3rd column)

MONTH: Month([Date])

Then in the criteria of this 3rd field put:

Month(Date())

that should set you up
hth

Ben

Oh, I got it now. I misunderstood. Thanks for the help!

The following criteria specifies current month:-

Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)

This will ensure that only the current month is shown (for this year) and not data for the current month from other years.

HTH's

Regards

anytime! Glad it worked

Excuse me jumping in. I would avoid using MONTH as a field name, I would use fldMonth or something similar.

This post really helped me set up a form that I am using to pull a report. My question is now that I can decide which month I want the report for how do I tell it what year? I tried having an unbound field on my call form that does a Dlookup on the [DATE_WORKED] field and just returned the year for the month (another unbound field) that exists in the data. I can get it to return the first year in the table but not in a form that the user can choose the year. Any suggestions?

Okay. After a little bit more thinking and reading I think that I have found a solution for my problem and thought I would post it to see if there is a better way.

I used this code to fill my combo box with the years that exist for the month chosen.

Me![cboYEAR].RowSource = "Select Distinct Year([WORK_DATE]) From [Monthly Work - Bucket]"

Then I used the month, year and employees name to pull the report.

This worked for me but I am not sure if it is the best way. I would appreciate any input.

Thanks,