473,324 Members | 2,196 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.

Checkboxes and Dropdowns in Queries (Access 2003)

greeni91
Hi All,

I am creating a database for one of my colleagues and he has asked me to make a query that has a drop down menu and takes into account that the checkboxes on the form have been ticked.

I have made a form for this task which has an unbound combo box and 2 command buttons. When I click the OK command button it will open the query using the selection I have made from the drop down.

I have made queries like this before but now I need to tell the query to show me the same information with my 3 checkboxes ticked.

When I run my form, make my selection and click OK the query shows me nothing. I know that there are records with this criteria as I have looked throught the table and noted at least 8.

I have posted a copy of my SQL statement below. (Hope it helps).

Expand|Select|Wrap|Line Numbers
  1. SELECT [Problem Reporting Table].[Problem Number], [Problem Reporting Table].[IFA 1 - Who?], [Problem Reporting Table].[IFA 1 - Completed?], [Problem Reporting Table].[IFA 2 - Who?], [Problem Reporting Table].[IFA 2 - Completed?], [Problem Reporting Table].[IFA 3 - Who?], [Problem Reporting Table].[IFA 3 - Completed?], [Problem Reporting Table].[PRA 1 - Who?], [Problem Reporting Table].[PRA 1 - Completed?], [Problem Reporting Table].[PRA 2 - Who?], [Problem Reporting Table].[PRA 2 - Completed?], [Problem Reporting Table].[PRA 3 - Who?], [Problem Reporting Table].[PRA 3 - Completed?]
  2. FROM [Problem Reporting Table]
  3. WHERE ((([Problem Reporting Table].[IFA 1 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[IFA 1 - Completed?]) Like "0")) OR ((([Problem Reporting Table].[IFA 2 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[IFA 2 - Completed?]) Like "0")) OR ((([Problem Reporting Table].[IFA 3 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[IFA 3 - Completed?]) Like "0")) OR ((([Problem Reporting Table].[PRA 1 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[PRA 1 - Completed?]) Like "0")) OR ((([Problem Reporting Table].[PRA 2 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[PRA 2 - Completed?]) Like "0")) OR ((([Problem Reporting Table].[PRA 3 - Who?])=[Forms]![frmOAOperator]![cboOperator]) AND (([Problem Reporting Table].[PRA 3 - Completed?]) Like "0"))
  4. ORDER BY [Problem Reporting Table].[Problem Number];
  5.  
Thanks in Advance,

/Sandy
Apr 13 '10 #1
1 2450
NeoPa
32,556 Expert Mod 16PB
Sandy,

I've tidied up the SQL for you so that it can be read (always a good idea if you want anyone to be able to help you easily). My version is not an exact copy, but is equivalent.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Problem Number],
  2.          [IFA 1 - Who?],
  3.          [IFA 1 - Completed?],
  4.          [IFA 2 - Who?],
  5.          [IFA 2 - Completed?],
  6.          [IFA 3 - Who?],
  7.          [IFA 3 - Completed?],
  8.          [PRA 1 - Who?],
  9.          [PRA 1 - Completed?],
  10.          [PRA 2 - Who?],
  11.          [PRA 2 - Completed?],
  12.          [PRA 3 - Who?],
  13.          [PRA 3 - Completed?] 
  14.  
  15. FROM     [Problem Reporting Table] 
  16.  
  17. WHERE    (([IFA 1 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  18.   AND    ([IFA 1 - Completed?] Like "0"))
  19.    OR    (([IFA 2 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  20.   AND    ([IFA 2 - Completed?] Like "0"))
  21.    OR    (([IFA 3 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  22.   AND    ([IFA 3 - Completed?] Like "0"))
  23.    OR    (([PRA 1 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  24.   AND    ([PRA 1 - Completed?] Like "0"))
  25.    OR    (([PRA 2 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  26.   AND    ([PRA 2 - Completed?] Like "0"))
  27.    OR    (([PRA 3 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  28.   AND    ([PRA 3 - Completed?] Like "0")) 
  29.  
  30. ORDER BY [Problem Number]
What I see nothing of here anywhere, is any reference to anything that looks like any CheckBox controls on your form. Nor do I see why you are comparing your [... - Completed?] fields with a string value of '0'. You appreciate that usage of Like in that context, without any wild-cards is equivalent to equals (=), yes?
Apr 13 '10 #2

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

Similar topics

10
by: Marco Alting | last post by:
Hi, I'm still confused about my queries, I want to do something is ASP that is easily done in Access. I'll post the Access queries below as a reference. The main idea is that the queries depend...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
8
by: Ralph Freshour | last post by:
I have multiple checkbox's created with an array name because I have many on the same web page - their names are like: frm_chk_delete frm_chk_delete frm_chk_delete frm_chk_delete etc. Here...
5
by: Deborah V. Gardner | last post by:
I would like to use "Yes" and "No" checkboxes on a subform. The problem is that when I click the Yes checkbox on the subform, all of the checkboxes are checked. Currently, I have a field...
5
by: Jerry Hull | last post by:
I'm working with a database developed by an untrained person over several years - and on a network that has recently been upgraded with a new server installed and MS office upgraded from 2K (I...
7
by: DJ Dev | last post by:
Hi All, I have a complex problem. I have dropdownlists (usually 3-5) and the user selects some value from these and for each value selected, datagrids are shown to the user. I am creating the...
3
by: Kevin Forbes | last post by:
So, I've found how to list all the tables in an Access database (as seen below) and running MS Access queries is easy (similar to executing a stored procedure), but how do I list the names of all...
3
prn
by: prn | last post by:
Hi folks, I'm trying to create letters based on an Access database. I'm using Access 2003 and Word 2003. Most of the data tables are actually on a SQL server and Access is the front end for them....
4
by: Yitzak | last post by:
Have a query that is arregated into crosstab query, it is working correctly. select Yes as selected from tblname or select true as selected from tblname Produces -1 ( vba value of True) in...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
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
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...

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.