By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,448 Members | 878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,448 IT Pros & Developers. It's quick & easy.

Checkboxes and Dropdowns in Queries (Access 2003)

greeni91
P: 61
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
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,768
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

Post your reply

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