By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,634 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,634 IT Pros & Developers. It's quick & easy.

Filter/Query tables with multiple combo boxes within date range

P: 3
Hi all,

I'm taking on an Access project to track customer service interactions (phone calls, emails, walk in customers) as well as hours worked in active and inactive roles, that will replace multiple Excel spreadsheets.

I've had a few Access classes as part of an MIS minor years ago, but I'm stumped on how to approach creating a form that would either query or filter my tblActivity and tblUsers tables by date range and some other criteria such as the employee ID, the day of the week, the campus that the agent works on, and the type of position (manager, full time, student worker, etc.)

I've tried a number of approaches shown at https://bytes.com/topic/access/answers/834132-create-query-multiple-combo-boxes along with a number of other websites over the last two weeks, and I figured it was time to ask this group of experts on how to approach this.

I was unable to attach the database itself, but I've included a Word doc with multiple screen grabs, and the first part of the SQL that seems to be okay. Happy to provide any additional information needed, and I'm grateful for any help that you can provide!!

Scott
Attached Files
File Type: docx CS Contacts database- Bytes.docx (237.7 KB, 8 views)
1 Week Ago #1
Share this Question
Share on Google+
6 Replies

isladogs
Expert
P: 58
Hi
I have several examples on my website that may help.
Due to forum rules, I'm not allowed to post a link but if you do a google search for Multiple Group & Filter Mendip Data Systems, the first result should find the correct page.

EDIT:
Attached is a low resolution screenshot showing one example with multiple combobox filters and a selected date range
Attached Images
File Type: png Capture.PNG (140.9 KB, 12 views)
1 Week Ago #2

isladogs
Expert
P: 58
BTW You can attach a database if its not too large and you zip it before uploading
1 Week Ago #3

P: 3
Isladogs- this is really impressive, and WAY beyond what I thought was possible. I'll spend some time with this and see if I can either learn enough to update what I have, or if I could modify this enough to work for my purposes.

Thank you for the tip re: uploading zipped Access files- I'll attach mine here. Data is just generic. Is there a simple way to make mine work?

Very grateful to you and to all the experts on Bytes who help people like me out! Looking to develop my skills, and sometimes you need some coaching from a pro.

Scott
Attached Files
File Type: zip CS Contacts for Bytes.zip (56.2 KB, 6 views)
1 Week Ago #4

isladogs
Expert
P: 58
You should be able to use a similar idea to my Incident Analysis example.
Add a subform to frmSearch with its record source the same as your query qrySearch. At first it will be unfiltered (like your query.

Replace the Run Query button with a Filter button which will run the code you need for multiple filtering. I recommend you don't try to do it all from a query

The filtering is built up by combining filter strings depending on the value of each combo and the start and end date. Study my example carefully for guidance on how it can be done.

To make it easier to troubleshoot the code I recommend you deal with one filter at a time and get each working. Then start to combine the filter strings remembering you to need to handle each datatype differently.

Dates are particularly tricky. If you live outside the USA, you need to convert all dates to mm/dd/yyyy format for the sql filter string. Again see my example.

Good luck
1 Week Ago #5

P: 3
Thank you again Isladogs! It sounds somewhat daunting, but your advice on how to proceed sounds like a good approach. The idea of using a toggle for some of the parameters in my query(NetID, Campus, Job) makes sense, since I would only be looking for one of these at a time. Thank you for providing such a great example- it really is very similar to what I'm trying to do.

Scott
1 Week Ago #6

isladogs
Expert
P: 58
You're welcome. It should be a good learning experience
However, I didn't mention a toggle!
6 Days Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.