Aggregating months and years into a combo box

[carl6885]

Hi

I am relatively new to access and can do stuff to an intermediate level.

I need to put months and year into a combo box based on the dates in a database.

In one column I have the dates which can be anything over the last couple of years.

What I want to do is allow the user to select a month in the drop down box to act as a parameter in a query to run a report.

Selecting the date and putting it into a query is easy, I can also format the date to show only the month and year i.e August 2011.

Doing this gives me loads of entries including many duplicates - all I want is one entry for each month and year represented in the db adding to the combo box.

Does anyone have any idea how to do this???

This is an extract of my data:

01/10/2011 October 2011
25/10/2011 October 2011
06/11/2011 November 2011 etc etc

Any help would be great.

Thanks

Carl

 

[nanscombe]

Hi Carl,

The attached file should give you an example of what you are trying to achieve in qryExtractUniqueDates.

To get rid of the duplicate values you need to ensure that the property Unique Values of the query is set to Yes.

The second column allows you sort them into the correct order. If you only want the first column showing in the combo then set the Combobox's column Count to 1.

I hope this useful.

Nigel

 

[carl6885]

Hi Nigel

Thanks for this - it is doing what I want.

I just have one further question on the matter.....

It is putting them in order but I get this:

DateValueSort

April 2011
August 2011
December 2011
February 2011
July 2011
November 2011
October 2010
October 2011
September 2011

As you can see Oct 2010 and 2011 are together and this will continue as the database builds. Is there a way to stop this and put it in an order that is accurate ie all 2010 before 2011 etc??

Thanks again!

 

[nanscombe]

Aha, that's in alphabetical order.

DateValueSort needs to be Format([DateField],"yyyymm") to work properly.

It looks like you've got DateValueSort as Format([DateField],"mmmm yyyy")


First column should be DateValues: Format([DateField],"mmmm yyyy") - October 2010

Second column should be DateValueSort: Format([DateField],"yyyymm") - 201010

Then sort on the second column.

 

[carl6885]

Sorry that was a newbie error - I completely missed the point.

Thanks for your help on this one!!!

 

[nanscombe]

That's Ok. I'm sure most of us have been there.