473,324 Members | 2,541 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,324 software developers and data experts.

Define multiple search criteria from one form but only use selected

Hi
I have written a form that has 4 drop down selection boxes on it, with check boxes next to them. When a user selects something from the drop down box the check box is automatically selected. I want to perfom a query and based on what has been selected from the drop down boxes use this information as the criteria. If a user has only selected a supplier name then the query should return all products from that supplier, but if a user selects a supplier name and colour then only products from that supplier that are that colour should be returned. (if that makes any sense?) I can get the query to do what I want on an individual criteria basis, but it is the ability to test whether a check box has been ticked and then apply that criteria that is causing me a headache. Any help on this would be grately appreciated.
Nov 9 '09 #1
2 2563
MMcCarthy
14,534 Expert Mod 8TB
I don't think you will be able to do this with just a query. I would use a VBA routine to build the query into a string. Something like ....

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL as String
  3. Dim start as Boolean
  4.  
  5.     strSQL = "SELECT * FROM MyQuery "
  6.     start = False
  7.  
  8.     If Me.Checkbox1 = True Then
  9.         strSQL = "WHERE Supplier='" & Me.combobox1
  10.         start = True
  11.     End If
  12.  
  13.      If Me.Checkbox2 = True Then
  14.         If start = True Then
  15.             strSQL = "AND City='" & Me.combobox2
  16.         Else    
  17.             strSQL = "WHERE City='" & Me.combobox2
  18.             start = True
  19.         End If
  20.     End If   
  21.  
  22.     DoCmd,RunSQL strSQL
  23.  
  24.  
This is just a rough idea to get you started. How you implement the query will depend on your requirements.

Mary
Nov 9 '09 #2
Thanks for that I've been away for a few days but will give it a go.
Nov 11 '09 #3

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

Similar topics

2
by: Vishy | last post by:
Hi One of the less documented features of xpath is how to run multiple search on nodes or attributes e.g <Book name="Harry Potter" price="10GBP"/> <Book name="Harry Potter" price="5USD"/> now...
1
by: TH | last post by:
I am (still :) working on a recipe database. Now I am trying to figure out how to set it up for an ingredient search. What I want it to be able to do is three things: 1. Search based on just...
1
by: Casey | last post by:
Hi, How would one allow for possible multiple search criteria using FREETEXTTABLE. For example, my table "listings" has a full-text search catalog, and I may want to: SELECT * FROM listings...
1
by: pramodrepaka | last post by:
I am using Vb6. I am using data report in my program I have it working but when I print it is printing all the records in the database. I am having troubles with it printing a single record. with the...
3
by: AishaKhalfan | last post by:
Hi, :wave: I have many search criteria in my program, such as: search by name, search by age, search by gender, search by city the user can search by name only, or by name and age, or by...
11
by: Panlflzs | last post by:
Howdy, I am working on a system where I need a basic HTML form to pass data to a cfm page. The cfm page will then query a database and return any matching data. I am using MySQL. I have my HTML...
5
by: LC2007 | last post by:
Hello, Can someone please help me on how to create Search Criteria form, i can't find exactly what i am looking for!!! i need a search button! but i can't find anything that can help me!
8
by: salzan | last post by:
I have this code: strAny = " = " & rsTemp!YearId rsPerm.Find strAny and it works. However, when I do the following it doesn't strAny = " = " & rsTemp!YearId & " AND " & _ "...
11
by: woodey2002 | last post by:
This problem is driving me crazy. Hello there, i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.