Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-07-19
12
Medium Priority
?
191 Views
Last Modified: 2016-07-19
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.
0
Comment
Question by:toalexsandr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41719416
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
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 41719469
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.
0
 

Author Comment

by:toalexsandr
ID: 41719573
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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:toalexsandr
ID: 41719575
Shaun, its a good point. I think i may need to do that down the road.
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 41719580
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41719588
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]
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41719591
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")
0
 

Author Comment

by:toalexsandr
ID: 41719592
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."
0
 

Author Comment

by:toalexsandr
ID: 41719593
nothing. same error.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41719596
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")
0
 

Author Comment

by:toalexsandr
ID: 41719605
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
0
 

Author Closing Comment

by:toalexsandr
ID: 41719606
Works perfect. Thanks.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question