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).

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?]
FROM [Problem Reporting Table]
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"))
ORDER BY [Problem Reporting Table].[Problem Number];

Thanks in Advance,

/Sandy

[NeoPa]

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.

SELECT   [Problem Number],
         [IFA 1 - Who?],
         [IFA 1 - Completed?],
         [IFA 2 - Who?],
         [IFA 2 - Completed?],
         [IFA 3 - Who?],
         [IFA 3 - Completed?],
         [PRA 1 - Who?],
         [PRA 1 - Completed?],
         [PRA 2 - Who?],
         [PRA 2 - Completed?],
         [PRA 3 - Who?],
         [PRA 3 - Completed?] 

FROM     [Problem Reporting Table] 

WHERE    (([IFA 1 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([IFA 1 - Completed?] Like "0"))
   OR    (([IFA 2 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([IFA 2 - Completed?] Like "0"))
   OR    (([IFA 3 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([IFA 3 - Completed?] Like "0"))
   OR    (([PRA 1 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([PRA 1 - Completed?] Like "0"))
   OR    (([PRA 2 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([PRA 2 - Completed?] Like "0"))
   OR    (([PRA 3 - Who?]=[Forms]![frmOAOperator]![cboOperator])
  AND    ([PRA 3 - Completed?] Like "0")) 

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?