Query criteria based on a Check box

[Chris]

I am trying to create a query based on criterea entered into a Form (the
criterea are - Financial year, Location and Active) I want the query to
return Active records only if the checkbox is ticked and all records if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

 

[John W. Vinson]

I am trying to create a query based on criterea entered into a Form (the
criterea are - Financial year, Location and Active) I want the query to
return Active records only if the checkbox is ticked and all records if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

Try a criterion on Active of

[Active] = [Forms]![YourFormName]![Active] OR (Not
[Forms]![YourFormName]![Active])

 

[Chris]

Thanks John - I tried this and I get no values in the query regardless of
wether the check box is ticked or not.

I am trying to create a query based on criterea entered into a Form (the
criterea are - Financial year, Location and Active) I want the query to
return Active records only if the checkbox is ticked and all records if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

Try a criterion on Active of

[Active] = [Forms]![YourFormName]![Active] OR (Not
[Forms]![YourFormName]![Active])

 

[John W. Vinson]

Thanks John - I tried this and I get no values in the query regardless of
wether the check box is ticked or not.

I am trying to create a query based on criterea entered into a Form (the
criterea are - Financial year, Location and Active) I want the query to
return Active records only if the checkbox is ticked and all records if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

Try a criterion on Active of

[Active] = [Forms]![YourFormName]![Active] OR (Not
[Forms]![YourFormName]![Active])

Please post the actual complete SQL of your query, the name of the form and
the form checkbox, and the name of the table field. Something isn't matching
up correctly!

 

[Chris]

Hi John - sorry i should have put that in my repy;

SELECT tblESGProjectInformation.ESGProjectID,
tblESGProjectInformation.ESGProjectName, [tblStaffDetails]![FName] & " " &
[tblStaffDetails]![LName] AS [Project Manager],
tblESGProjectInformation.Active
FROM (tblESGBranch INNER JOIN tblESGProjectInformation ON
tblESGBranch.ID_ESGBranch = tblESGProjectInformation.ID_ESGBranch) INNER JOIN
(tblStaffDetails INNER JOIN tblProjectStaffRelationships ON
tblStaffDetails.ID_StaffDetails =
tblProjectStaffRelationships.ID_StaffDetails) ON
tblESGProjectInformation.Id_Projects =
tblProjectStaffRelationships.ID_Projects
ORDER BY tblESGProjectInformation.ESGProjectID;

Thanks John - I tried this and I get no values in the query regardless of
wether the check box is ticked or not.

I am trying to create a query based on criterea entered into a Form (the
criterea are - Financial year, Location and Active) I want the query to
return Active records only if the checkbox is ticked and all records if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

Try a criterion on Active of

[Active] = [Forms]![YourFormName]![Active] OR (Not
[Forms]![YourFormName]![Active])

Please post the actual complete SQL of your query, the name of the form and
the form checkbox, and the name of the table field. Something isn't matching
up correctly!

 

[John W. Vinson]

Hi John - sorry i should have put that in my repy;

SELECT tblESGProjectInformation.ESGProjectID,
tblESGProjectInformation.ESGProjectName, [tblStaffDetails]![FName] & " " &
[tblStaffDetails]![LName] AS [Project Manager],
tblESGProjectInformation.Active
FROM (tblESGBranch INNER JOIN tblESGProjectInformation ON
tblESGBranch.ID_ESGBranch = tblESGProjectInformation.ID_ESGBranch) INNER JOIN
(tblStaffDetails INNER JOIN tblProjectStaffRelationships ON
tblStaffDetails.ID_StaffDetails =
tblProjectStaffRelationships.ID_StaffDetails) ON
tblESGProjectInformation.Id_Projects =
tblProjectStaffRelationships.ID_Projects
ORDER BY tblESGProjectInformation.ESGProjectID;

Eh? This query has no criteria on any field. It should return all the records
which have matching values in all of the tables (perhaps there are no such
records).

If you look at the design grid is there anything at all in the Criteria line?

 

[Chris]

Sorry - I was changing criterea to see if I could do it with an option group...

SELECT tblESGProjectInformation.ESGProjectID,
tblESGProjectInformation.ESGProjectName, [tblStaffDetails]![FName] & " " &
[tblStaffDetails]![LName] AS [Project Manager],
tblESGProjectInformation.Active
FROM tblESGProjectInformation INNER JOIN (tblStaffDetails INNER JOIN
tblProjectStaffRelationships ON tblStaffDetails.ID_StaffDetails =
tblProjectStaffRelationships.ID_StaffDetails) ON
tblESGProjectInformation.Id_Projects =
tblProjectStaffRelationships.ID_Projects
WHERE
(((tblESGProjectInformation.Active)=[Forms]![frmESGProjectYearBranchStatus]![chkActive]
Or
(tblESGProjectInformation.Active)<>[Forms]![frmESGProjectYearBranchStatus]![chkActive]))
ORDER BY tblESGProjectInformation.ESGProjectID;

Hi John - sorry i should have put that in my repy;

SELECT tblESGProjectInformation.ESGProjectID,
tblESGProjectInformation.ESGProjectName, [tblStaffDetails]![FName] & " " &
[tblStaffDetails]![LName] AS [Project Manager],
tblESGProjectInformation.Active
FROM (tblESGBranch INNER JOIN tblESGProjectInformation ON
tblESGBranch.ID_ESGBranch = tblESGProjectInformation.ID_ESGBranch) INNER JOIN
(tblStaffDetails INNER JOIN tblProjectStaffRelationships ON
tblStaffDetails.ID_StaffDetails =
tblProjectStaffRelationships.ID_StaffDetails) ON
tblESGProjectInformation.Id_Projects =
tblProjectStaffRelationships.ID_Projects
ORDER BY tblESGProjectInformation.ESGProjectID;

Eh? This query has no criteria on any field. It should return all the records
which have matching values in all of the tables (perhaps there are no such
records).

If you look at the design grid is there anything at all in the Criteria line?

 

[John W. Vinson]

(((tblESGProjectInformation.Active)=[Forms]![frmESGProjectYearBranchStatus]![chkActive]
Or
(tblESGProjectInformation.Active)<>[Forms]![frmESGProjectYearBranchStatus]![chkActive]))

Heh. That will return all records regardless, and it's NOT what I suggested.

Try

(((tblESGProjectInformation.Active)=[Forms]![frmESGProjectYearBranchStatus]![chkActive]
Or
([Forms]![frmESGProjectYearBranchStatus]![chkActive]) = False)

Of course if the query without ANY criteria returns no records, this query
won't either. Are you sure all your tables have data with matching values?

 

[totoche]

Hi John - sorry i should have put that in my repy;

SELECT tblESGProjectInformation.ESGProjectID,
tblESGProjectInformation.ESGProjectName, [tblStaffDetails]![FName] & " " &
[tblStaffDetails]![LName] AS [Project Manager],
tblESGProjectInformation.Active
FROM (tblESGBranch INNER JOIN tblESGProjectInformation ON
tblESGBranch.ID_ESGBranch = tblESGProjectInformation.ID_ESGBranch) INNER
JOIN
(tblStaffDetails INNER JOIN tblProjectStaffRelationships ON
tblStaffDetails.ID_StaffDetails =
tblProjectStaffRelationships.ID_StaffDetails) ON
tblESGProjectInformation.Id_Projects =
tblProjectStaffRelationships.ID_Projects
ORDER BY tblESGProjectInformation.ESGProjectID;

Thanks John - I tried this and I get no values in the query regardless
of
wether the check box is ticked or not.

:

On Thu, 5 Mar 2009 14:19:02 -0800, Chris
<[email protected]>
wrote:

I am trying to create a query based on criterea entered into a Form
(the
criterea are - Financial year, Location and Active) I want the query
to
return Active records only if the checkbox is ticked and all records
if it is
not. Is it possible to set the critera of the query to allow this?

Thanks in advance

Try a criterion on Active of

[Active] = [Forms]![YourFormName]![Active] OR (Not
[Forms]![YourFormName]![Active])

Please post the actual complete SQL of your query, the name of the form
and
the form checkbox, and the name of the table field. Something isn't
matching
up correctly!