473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Filter/Query tables with multiple combo boxes within date range

3 2Bits
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, 64 views)
Nov 21 '20 #1
6 2283
isladogs
455 Expert Mod 256MB
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, 103 views)
Nov 22 '20 #2
isladogs
455 Expert Mod 256MB
BTW You can attach a database if its not too large and you zip it before uploading
Nov 22 '20 #3
Scott1959
3 2Bits
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, 39 views)
Nov 22 '20 #4
isladogs
455 Expert Mod 256MB
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
Nov 23 '20 #5
Scott1959
3 2Bits
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
Nov 23 '20 #6
isladogs
455 Expert Mod 256MB
You're welcome. It should be a good learning experience
However, I didn't mention a toggle!
Nov 24 '20 #7

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

Similar topics

2
by: Damon Cherne | last post by:
Here is my issue. I have created a form called “Search” that has several unbound combo boxes, one for each of the attribute that match a column in the Table . Each combo box has input from a...
2
by: visionstate | last post by:
Hi there, I am working on a form that uses 3 text boxes and 3 combo boxes. When any data is entered into any of these, I click a command button and this requeries a sub query in the form and...
1
by: Shawn Yates | last post by:
It has been a while since I have done anything on MS Access and I seem to be a bit rusty. I hope someone could help me solve my issue. I have a form that has multiple combo boxes on it. Each box...
1
by: JC21 | last post by:
Hi guys, I was wondering if someone could give me some insight on this. On a form I would like to have 3 combo boxes which can be used as filters. I would like the information to be displayed on a...
9
WyvsEyeView
by: WyvsEyeView | last post by:
I have a form that has four unbound combo boxes: cboType, cboVersion, cboStatus, cboReview. I want to write a query that basically lets users make selections in as many combo boxes as apply...only...
1
by: Yasmeen Pannu | last post by:
HI, I am new to MS Access and stuck in a problem from few days. My Question: I have 3 combo box (Submitter, date from and date to) on my form and i want that when i select values from each of...
0
by: Svetlin Panov | last post by:
Hi Guys, I have a form with 48 Combo Boxes on it and I am filling them from the same range. I want to remove the values that are already selected not be available in the next ComboBox. I have...
1
by: srinivasgoud | last post by:
Hi, I have a Ms Access project, were I want to display the data from selected Item from combo box for given date range by start date and End date. or I want to put radio buttons for options what I...
5
by: laht0028 | last post by:
Hello, I'm attempting to create a form where the multiple combo boxes are cleared when I proceed to the next record. I have applied the below code and it works to clear one of the combo boxes but...
4
by: rfstott | last post by:
This database is used to track TV series that are being watched or have been watched. I have multiple combos on a tab form to to use as filter options. After making selection in the verious combo...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.