Dec 18 2003, 11:54 AM
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.
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?
Dec 18 2003, 12:28 PM
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.
Dec 18 2003, 12:49 PM
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.
Dec 18 2003, 12:52 PM
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
Add a field to your query like so:
where datefield is the date you want to filter and sets its criteria to:
which will filter to the current month (based on system date)
Dec 18 2003, 01:04 PM
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?
Dec 18 2003, 01:07 PM
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)
Then in the criteria of this 3rd field put:
that should set you up
Dec 18 2003, 01:08 PM
Oh, I got it now. I misunderstood. Thanks for the help!
Dec 18 2003, 01:12 PM
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.
Dec 18 2003, 01:12 PM
anytime! Glad it worked
Dec 18 2003, 01:45 PM
Excuse me jumping in. I would avoid using MONTH as a field name, I would use fldMonth or something similar.
Dec 29 2003, 08:33 AM
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?
Dec 29 2003, 09:55 AM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here