Query/Filter by Date

[jasonbarnes]

I have a table in my database that has a date field (we'll call salesdate). I use this table in a query, which is in turn linked to an excel spreadsheet and turned in a pivot table.



Here is my question. The salesdate field is formatted as XX/XX/XXXX. The pivot table shows the entire tables data. I would like to be able to filter in the pivot table by month instead of having to click 30 days each month. So the end user could go the filter or control, and filter by August 2010 for example.

I read Excel 2010 has a new type of filter where you could filter by a date range but I am using 2007. I also played with changing the database itself or the query in Access. Could I have an Expr field that modifies the salesdate field? This would in turn create a better field to use a filter by having a list (August 2010, September 2010, or ...Sept-10, Aug-10 etc.

Any advice on the most practical solution is appreciated.

[June7]

Could use an expression to construct a field that returns a string value of Month and Year.

[Stingaway]

Excel 2007 has this ability as well. You just need to add a column to your pivot data that uses the year function on the date field in question. From there add it to your pivot table and you can filter that in the drop down by selecting the column in the data first...

[jasonbarnes]

I used

Month: Month(SalesDate) to get the month and
Year: Year(SalesDate) to get the year

Is there a simple trick to convert the number to text? (In either the query or pivot).. I tried formatting the column as text or date but the close I can get is... "January-00"

[Stingaway]

For the month column in an access query you can use the MonthName function.

Example, Monthname(1) will yield "January".

[Stingaway]

So in this case in Access you could actually say Monthname(Month(SalesDate))

[jasonbarnes]

Perfect! Thanks for the help.