Filtering a pivot chart

[hunterfan48]

I would like a pivot chart to only return values within the current month.

I can get the pivot chart to show me all values and their respective datees. I'd like to have this pivot chart filtered to only show values that match with the actual current month. In this case, only return values with the month of November. Next month would be December and so forth.

Is this possible??

Thanks!

 

[vbaInet]

Base your pivot chart on a query and apply a Month() filter within the query.

 

[hunterfan48]

So by putting Month() in the criteria section of a query, it will return results within the current month?

I can see putting the month's name in there, but then that wouldn't work when the month changes as far as I know.

Please advise and thanks for your help!

 

[vbaInet]

Read the help files about the Month() function or Google it on TechOnTheNet.com. The month of your date field needs to be set to the month of Date()

 

[hunterfan48]

Let's just make sure we're on the same page before proceeding.

I know by putting in the number, it would bring up the correct month. For example,
If I put in Month(3) it would return all values for the month of March. However, we are in the month of November. I would like this function to return values from the current month. So, whatever code or function I need to put in to tell Access to 1st) find out what's the current month and then return all values within the current month.

I do not want to have to go in and update this...I want it to update monthly as the months change.

Tell me if we're on the same page now...

Thank you!
Brady

 

[vbaInet]

I know by putting in the number, it would bring up the correct month. For example,
If I put in Month(3) it would return all values for the month of March.

It won't. Month("1/3/2011") will.

Tell me if we're on the same page now...

We are. Re-read my last post and read the help files and it will dawn on you Brady.

 

[hunterfan48]

Ok...I'll keep working at it.

Thank you

 

[vbaInet]

Let us know if you need further clarification or assistance.

 

[hunterfan48]

Will do...thank you! I think after re-reading your post I get what you're saying. I'm gonna go test it out.

 

[hunterfan48]

http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

This was helpful to me...

 

[vbaInet]

Glad to hear!

 

[hunterfan48]

Thank you again for your help!

What I'm wondering though is there must be more than one way to do this right? Haha like the old saying goes 'more than one way to skin a cat right?' LOL

Reason is because I put this formula in my query design under the date field to find the records for the current year.

Year([Date]) = Year(Date())

After running the query and going back to design view, I noticed it had erased that, but put the following in a brand new column. In the top field section, it put the following.

Year([Date])

Then in the criteria, it had this.

Year(Now())

Why would it do this??


Here's another way I found out how to do this on my own after you had helped. I went in and filtered my table that this query was based off. From the filtered table, I then went into the 'Advanced Filter & Sort' to see what they had done to filter it.

 

[vbaInet]

After running the query and going back to design view, I noticed it had erased that, but put the following in a brand new column. In the top field section, it put the following.

Year([Date])

Then in the criteria, it had this.

Year(Now())

Why would it do this??

Because that's how it should actually be. So don't worry, it's correct that way.

Here's another way I found out how to do this on my own after you had helped. I went in and filtered my table that this query was based off. From the filtered table, I then went into the 'Advanced Filter & Sort' to see what they had done to filter it.

Good work!

 

[hunterfan48]

Thanks for the compliment! Dare I say I'm learning...all thanks to the wonderful community!

Thank you all for your help!

 

[vbaInet]

You're welcome!