473,836 Members | 1,460 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Query to Accept multiple check box parameters

1 New Member
Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you
Nov 9 '07 #1
1 11517
Lysander
344 Recognized Expert Contributor
Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you
I would create a function that built the criteria up field by field and call this function in the after_update event of each field. Something like this
Expand|Select|Wrap|Line Numbers
  1. sub SetCriteria()
  2. dim strSQL as string
  3. strSQL=""
  4. if Field1 then strSQL="Field1=true"
  5.  
  6. if Field2 then
  7.   if len(strSQL)>0 strSQL=strSQL & " OR "
  8.   strSQL=strSQL & "Field2=true"
  9.  
  10. if Field3 then
  11.   if len(strSQL)>0 strSQL=strSQL & " OR "
  12.   strSQL=strSQL & "Field3=true"
  13.  
  14. etc.
  15.  
  16. if len(strSQL)>0 then strSQL= " WHERE " & strSQL
  17.  
  18. me.recordsource="Select * from tblSomething " & strSQL & ";"
  19. end sub
  20.  
or use the strSQL as the where critieria in your query
Nov 9 '07 #2

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

Similar topics

2
1854
by: Jeff Lowry | last post by:
I'm pasing a zip code as a prameter to an Access stored procedure. In Access the parameter is a text data type. It works for non-leading zero zip codes but, apparently access (or ASP) is converting it to a value first (dropping the zero) then sending that to my SP. Even if I use cStr() to be sure the parameter is sent a string it still seems to drop the leading zero. Any thoughts? Note: It needs to be a string for canadian zip...
6
4768
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much appreciated. Thanks in advance
3
6178
by: DFS | last post by:
I've been working around this for years (I believe), so I figured someone here might know: Why won't a crosstab query accept a value from a form reference? TRANSFORM Format(First(QtrAvg),'Fixed') AS FirstQtrAvg SELECT PropertyCode, Survey, Question FROM SurveyData WHERE PropertyCode = Forms.MainForm.Subform.Form.PropertyCode GROUP BY PropertyCode, Survey, Question
1
1831
by: Jim Heavey | last post by:
If I trying to write code which is not dependant on which database I am using. I have developed the code for SQL Server and it is working just fine, and now I have created the same data in MS Access. I am currently running all stored procedures. I know that I have run "procedures" in access, but I do not quite remember how to set this up. When I build the query in Access, how do I identify a parameter value is required and where? Do I...
6
5480
by: InnoCreate | last post by:
Hi everyone. I've recently written a classic asp website which uses an MS Access datasource. I know this is less than an ideal data source as it has limited functionality. I have a search form on my website which allows users to define parameters and return results accordingly. The problem i have is a need to return these results in a random order each time. With SQLServer i know NEWID() would do the trick - used this many times before...
9
3067
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
4
12455
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
4
3530
by: JBiggsCC | last post by:
I have a very simple login page which takes an ID number via a HTML form GET. What is easiest way to check that ID number against an Access DB to see if it exists? I want to redirect with the ID in the query string if it does exist and have them re-enter if incorrect.
0
1385
by: BSB | last post by:
Hi, I generate a "Find Duplicate" query for one table that will return some records I want to capture those records in my VB code..... This is my code...pls guide me how to proceed.... *************************************************************************************** adocmd.CommandText = sQueryName adocmd.CommandType = adCmdStoredProc
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10845
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10592
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9376
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4456
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4019
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.