Checkbox & Textbox Used As Criteria For A Query

[hfrupn]

I need to see all records where the field AssignTo is null or has been
assigned to a specific person. If the checkbox is true all records with null
value AssignTo are displayed. If the textbox [Forms]![frmReview]![AssignedTo]
is null with the checkbox true only the null AssignTo records are displayed
but if there is a value in the [Forms]![frmReview]![AssignedTo] then all null
AssignTo and records with the specific value are displayed.
If both the checkbox and textbox are null all records are displayed and
lastly if the checkbox is false and there is a value in the textbox only
those specific records are displayed.

I have tried the following SQL but I’m not having much luck.

WHERE
(((qReview03ClosedItems.UserAssigned)=IIf([Forms]![frmReview]![NotAssigned]=True,(qReview03ClosedItems.UserAssigned)
Is Null,"") Or
(qReview03ClosedItems.UserAssigned)=[Forms]![frmReview]![AssignedTo])) OR
((([Forms]![frmReview]![AssignedTo]) Is Null));

I would really appreciate any assistance.

Nick

 

[KARL DEWEY]

I do not see a need for the checkbox as you always want to see any Nulls in
the UserAssigned.
Try this --
WHERE qReview03ClosedItems.UserAssigned=[Forms]![frmReview]![AssignedTo] OR
qReview03ClosedItems.UserAssigned Is Null OR
qReview03ClosedItems.UserAssigned Like IIF(=[Forms]![frmReview]![AssignedTo]
Is Null, "*",);

 

[hfrupn]

Karl,
Your suggestion works well but I still have a need to filter in or out
un-assigned records as well as being able to see records for a specific user
at the same time. I thought a checkbox would be the best way to achieve this.
If you have any other suggestions I would appreciate it.

Regards
Nick

I do not see a need for the checkbox as you always want to see any Nulls in
the UserAssigned.
Try this --
WHERE qReview03ClosedItems.UserAssigned=[Forms]![frmReview]![AssignedTo] OR
qReview03ClosedItems.UserAssigned Is Null OR
qReview03ClosedItems.UserAssigned Like IIF(=[Forms]![frmReview]![AssignedTo]
Is Null, "*",);
--
KARL DEWEY
Build a little - Test a little

I need to see all records where the field AssignTo is null or has been
assigned to a specific person. If the checkbox is true all records with null
value AssignTo are displayed. If the textbox [Forms]![frmReview]![AssignedTo]
is null with the checkbox true only the null AssignTo records are displayed
but if there is a value in the [Forms]![frmReview]![AssignedTo] then all null
AssignTo and records with the specific value are displayed.
If both the checkbox and textbox are null all records are displayed and
lastly if the checkbox is false and there is a value in the textbox only
those specific records are displayed.

I have tried the following SQL but I’m not having much luck.

WHERE
(((qReview03ClosedItems.UserAssigned)=IIf([Forms]![frmReview]![NotAssigned]=True,(qReview03ClosedItems.UserAssigned)
Is Null,"") Or
(qReview03ClosedItems.UserAssigned)=[Forms]![frmReview]![AssignedTo])) OR
((([Forms]![frmReview]![AssignedTo]) Is Null));

I would really appreciate any assistance.

Nick

 

[KARL DEWEY]

Ok, say again when would you not want to see un-assigned records?
--
KARL DEWEY
Build a little - Test a little

Karl,
Your suggestion works well but I still have a need to filter in or out
un-assigned records as well as being able to see records for a specific user
at the same time. I thought a checkbox would be the best way to achieve this.
If you have any other suggestions I would appreciate it.

Regards
Nick

I do not see a need for the checkbox as you always want to see any Nulls in
the UserAssigned.
Try this --
WHERE qReview03ClosedItems.UserAssigned=[Forms]![frmReview]![AssignedTo] OR
qReview03ClosedItems.UserAssigned Is Null OR
qReview03ClosedItems.UserAssigned Like IIF(=[Forms]![frmReview]![AssignedTo]
Is Null, "*",);
--
KARL DEWEY
Build a little - Test a little

I need to see all records where the field AssignTo is null or has been
assigned to a specific person. If the checkbox is true all records with null
value AssignTo are displayed. If the textbox [Forms]![frmReview]![AssignedTo]
is null with the checkbox true only the null AssignTo records are displayed
but if there is a value in the [Forms]![frmReview]![AssignedTo] then all null
AssignTo and records with the specific value are displayed.
If both the checkbox and textbox are null all records are displayed and
lastly if the checkbox is false and there is a value in the textbox only
those specific records are displayed.

I have tried the following SQL but I’m not having much luck.

WHERE
(((qReview03ClosedItems.UserAssigned)=IIf([Forms]![frmReview]![NotAssigned]=True,(qReview03ClosedItems.UserAssigned)
Is Null,"") Or
(qReview03ClosedItems.UserAssigned)=[Forms]![frmReview]![AssignedTo])) OR
((([Forms]![frmReview]![AssignedTo]) Is Null));

I would really appreciate any assistance.

Nick

 

[hfrupn]

I hope this may clarify what I’m trying to achieve. 1, is with the checkbox,
[NotAssigned] is un-checked. 2, is with the checkbox, [NotAssigned] checked.
I can’t see any other way of displaying the four scenarios.

(1) Checkbox [Forms]![frmReview]![NotAssigned]= False
[Forms]![frmReview]![AssignedTo] is null = All records displayed
[Forms]![frmReview]![AssignedTo] person’s name = Only records for that person

(2) Checkbox [Forms]![frmReview]![NotAssigned]= True
[Forms]![frmReview]![AssignedTo] is null = only records with no one assigned
[Forms]![frmReview]![AssignedTo] person’s name = all records with no one
assigned and records for that person
I thought a nested IIF statement might work but that is also out of my league.

Regards
Nick

Ok, say again when would you not want to see un-assigned records?
--
KARL DEWEY
Build a little - Test a little

Karl,
Your suggestion works well but I still have a need to filter in or out
un-assigned records as well as being able to see records for a specific user
at the same time. I thought a checkbox would be the best way to achieve this.
If you have any other suggestions I would appreciate it.

Regards
Nick

I do not see a need for the checkbox as you always want to see any Nulls in
the UserAssigned.
Try this --
WHERE qReview03ClosedItems.UserAssigned=[Forms]![frmReview]![AssignedTo] OR
qReview03ClosedItems.UserAssigned Is Null OR
qReview03ClosedItems.UserAssigned Like IIF(=[Forms]![frmReview]![AssignedTo]
Is Null, "*",);
--
KARL DEWEY
Build a little - Test a little


:

I need to see all records where the field AssignTo is null or has been
assigned to a specific person. If the checkbox is true all records with null
value AssignTo are displayed. If the textbox [Forms]![frmReview]![AssignedTo]
is null with the checkbox true only the null AssignTo records are displayed
but if there is a value in the [Forms]![frmReview]![AssignedTo] then all null
AssignTo and records with the specific value are displayed.
If both the checkbox and textbox are null all records are displayed and
lastly if the checkbox is false and there is a value in the textbox only
those specific records are displayed.

I have tried the following SQL but I’m not having much luck.

WHERE
(((qReview03ClosedItems.UserAssigned)=IIf([Forms]![frmReview]![NotAssigned]=True,(qReview03ClosedItems.UserAssigned)
Is Null,"") Or
(qReview03ClosedItems.UserAssigned)=[Forms]![frmReview]![AssignedTo])) OR
((([Forms]![frmReview]![AssignedTo]) Is Null));

I would really appreciate any assistance.

Nick