Populate access combo box with month name and year from data table.

toalexsandr
toalexsandr used Ask the Experts™
on
I have 32000 records in my table and I need my monthly report to show me all records for the month of June 2016 for example, or January 2015, just by selecting "January 2015" from combo box. This combo box needs to be sequentially populated when opening a report and if a new month is detected to add it to combo box.

Any help is appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
i'll assume you have a date field in your table, so use this as the rowsource of your combo box

select format([dateField], "mmmm yyyy")  from tableName
group by  format([dateField], "mmmm yyyy")
 order by  [dateField]

use a query as recordsource of your form/report using the value of the selected item form the combo box

select * from tableName
where  format([dateField], "mmmm yyyy") = Forms!NameofForm.NameofCombo
Shaun KlineLead Software Engineer

Commented:
From a usability stand point, consider what will happen if your application is used for 3, 5, 10 years. Think about the number of entries that will appear in the combo box. You may want to consider using two combo boxes, one for Month that you fill will all months, and one for year that you pull from your data.

Author

Commented:
Rey here is what I had to use because of errors but its not sorting it properly...

SELECT Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy") AS InspDate
FROM tb_WOInspectionNAS
GROUP BY Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy")
ORDER BY Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy");

April 2016
August 2015
December 2015
February 2016
January 2016
July 2015
July 2016
June 2015
June 2016
March 2016
May 2016
November 2015
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Shaun, its a good point. I think i may need to do that down the road.
Shaun KlineLead Software Engineer

Commented:
If you want to sort your dates in calendar order, change for your format in the order by to something like "yyyy-mm-dd". The group by may not like that though.
Top Expert 2016

Commented:
change the field in the order by

SELECT Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy") AS InspDate
FROM tb_WOInspectionNAS
GROUP BY Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy")
ORDER BY [tb_WOInspectionNAS].[QC Inspection Date]
Top Expert 2016

Commented:
change the field in the order by

SELECT Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy") AS InspDate
FROM tb_WOInspectionNAS
GROUP BY Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy")
ORDER BY Format([tb_WOInspectionNAS].[QC Inspection Date] , "yyyy mm")

Author

Commented:
i get this error "You tried to execute a query that does not include the specified expression '[tb_WOInspectionNAS].[QC Inspection Date]' as part of an aggregate function."

Author

Commented:
nothing. same error.
Top Expert 2016
Commented:
SELECT Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy") AS InspDate
FROM tb_WOInspectionNAS
GROUP BY Format([tb_WOInspectionNAS].[QC Inspection Date],"mmmm yyyy"), Format([tb_WOInspectionNAS].[QC Inspection Date] , "yyyy mm")
ORDER BY Format([tb_WOInspectionNAS].[QC Inspection Date] , "yyyy mm")

Author

Commented:
Worked perfect...Thanks a lot.

June 2015
July 2015
August 2015
November 2015
December 2015
January 2016
February 2016
March 2016
April 2016
May 2016
June 2016
July 2016

Author

Commented:
Works perfect. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today