Hi, I am creating a report in Access, where the users select checkboxes & they get the required data from table in the report. Below is the sql statement i have written: -
SELECT *
-
FROM tblMain
-
WHERE (((tblMain.[Column1]) LIKE[forms]![SearchReports]![Option1] & "*") AND ((tblMain.[Column2]) LIKE[forms]![SearchReports]![Option2] & "*") AND ((tblMain.[Column3]) LIKE[forms]![SearchReports]![Option3] & "*") AND ((tblMain.[Column4]) LIKE[forms]![SearchReports]![Option4] & "*"));
-
The SQL query works perfect if we select/check e.g. one Option only..however if I select Option 1 & 2 both, it doesn't display records, whereas I have records for both the options my table. If I use OR operator in my SQL query, it displays all records of table, regardless of whether they were CHECKED or not . any help would be appreciated as I can't see how to configure it. Thks.
9 3531
Anyone please? would appreciate suggestions.Thx s.
ChipR 1,287
Recognized Expert Top Contributor
What are you trying to compare with the LIKE operator?
Column 1, 2,3 & 4 are YES/NO fields in my table. And in my sql statement, - ((tblMain.[Column1]) LIKE[forms]![SearchReports]![Option1])
Option1, 2,3 & 4 are the names of my checkboxes on the search form. if I check the checkbox for Option 1, then it gives all record from column 1 on my report, like wise if I select OPTION 1 & 2 both on the form, then no record is displayed in the report, coz i am using the AND operator. But If i use OR instead of AND in the sql statement, then if I select OPTION 1 only, all the records related to OPTION 1,2 ,3 & 4 are displayed.
So basically I only want records on my report whose checkboxes are selected by me on the form. I hope i haven't confused you here..thxs for your help.
ChipR 1,287
Recognized Expert Top Contributor
Should the records returned match all of the check boxes, or do you want the records who have true values for the options that you checked, regardless of the other fields?
I only want records who have true values for the options that I checked. E.g. If I select checkbox 1 & 2 (Option 1& 2 on my form), so the REPORT should display all records for these 2 options & ignore OPTION 3 & 4 (since i didn't check them on form).
Thxs.
ChipR 1,287
Recognized Expert Top Contributor
In that case, you need to test the values of the check boxes, like:
WHERE
(Column1 = True OR Option1 = False)
AND
(Column2 = True OR Option2 = False)
AND
(Column3 = True OR Option3 = False)
AND
(Column4 = True OR Option4 = False)
Hopefully, that makes sense. If the check box on the form is True, then the value in the column must be true. Otherwise, it doesn't matter. I left out the Form references just to keep it shorter.
Hi ChipR, can you please write the exact sql statement for me? I haven't understood properly what you are saying here. Thxs
ChipR 1,287
Recognized Expert Top Contributor
This depends on the value that you have set your yes/no fields to save in your table. In design view for your table, look at one of the fields and its Format option on the General tab. I have mine set to True/False, so I'll compare the field value to True. Now, it turns out the value of the check box might be Null, so we have to check for that: - SELECT *
-
FROM tblMain
-
WHERE
-
([column1] = True OR (Forms!SearchReports!Option1 <> -1) OR Forms!SearchReports!Option1 IS NULL)
-
AND
-
([column2] = True OR (Forms!SearchReports!Option2 <> -1) OR Forms!SearchReports!Option2 IS NULL)
-
AND
-
([column3] = True OR (Forms!SearchReports!Option3 <> -1) OR Forms!SearchReports!Option3 IS NULL)
-
AND
-
([column4] = True OR (Forms!SearchReports!Option4 <> -1) OR Forms!SearchReports!Option4 IS NULL)
Basically, the condition is: If the check box is null, that field doesn't have to be true, OR if the check box is false (-1), that field doesn't have to be true. OR the field has to be true.
Thxs ChipR..it works great..really appreciate your help.
Cheers.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DylanM |
last post by:
I have some checkboxes that are generated from the results of a database search. At the moment, the checkboxes are part of a table making up a form. Users are going through the form, clicking the boxes and saving to the database at the end with the 'Submit' command button.
Is it possible to save the changes as the checkboxes are clicked? I suppose I'd need to write some dynamic ASP event handling at the same time as creating the checkboxes......
|
by: Incolor |
last post by:
Hello All!
I have to generate a checklist form as an input form in Access. A paper
form is taken out in the field and checked yes, no, OR n/a for each
item inspected. The problem I am having is this, the form should have a
main heading with the date, report no, and inspector. This information
should be entered only once in the form, but recorded for each item
inspected in the database table. Under the main heading there is
another...
|
by: EHameed |
last post by:
Hello Everyone,
Im a new member in this GREAT forum.
My question is:
I have a simple database that contains many checkboxes and in the Report i want to show only the checked CheckBoxes for each record..
I followed some of the discussions in the forum but it didn't work..
Will you help me please??
|
by: Lysander |
last post by:
Thought I would give something back with a few articles. This article is a bit of code to add error handling. When I have time, I want to write articles on multilingual databases, and Access Security, but I'll start with something short and simple
This code was written in Access 2003 but should be valid in Access 2000
By default, when you start a new module, either in a form or report, or a global module, Access does not declare Option...
|
by: krokador |
last post by:
This is doing my head in...
We're starting to migrate our report-printing and such (forms included) to pdf - using xml files and nFOP with asp.net. In this case I have to print out a form which contains a whole lot of checkboxes, so I made 2 gifs of 12x12 pixels that represent the empty and checked states. Because I use so many checkboxes, I thought about making a template with a parameter wether it should be checked or not (if the xml...
| |
by: GISmatters |
last post by:
I have unbound checkboxes in a nested gridview to allow multi-selection of "child" rows. For context, the parent gridview rows are for large "reports", the child rows are for various specific files comprising each report. I want the user to be able to select an arbitrary collection of report files and have them emailed by clicking an "Email selected files" button.
Everything displays properly, including the checkboxes for each child row (each...
|
by: WyvsEyeView |
last post by:
I have a report on which each record could potentially display three checkboxes...call them chk1, chk2, chk3. To minimize clutter on the page, I only want to display the labels of the checkboxes that are checked. I have not been able to find any way to test the value of these checkboxes using any of the report events. Access's conditional formatting doesn't allow you to hide a control based on conditions. Any ideas? Thanks!
|
by: zufie |
last post by:
I have to create a QA report regarding callers calling into a phone
hotline.
The report consists of many checkboxes such as:
Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know
|
by: curious80237 |
last post by:
In Access 2003: A table with four yes/no fields is part of a query used as data source for a report. The yes/no fields show in the datasheet and the report as checkboxes; the default value of the field is "no". The report pulls in a subreport. I want the subreport to be visible ONLY if any of the yes/no fields contain a yes (showing as a check in the checkbox). I tried an if then statement like this:
If Field1 ="no" AND Field2 = "no" AND...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
| |
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,...
|
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...
|
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...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |