|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 2nd, 2008, 02:40 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Checkbox & Textbox Used As Criteria For A Query
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 
|
|
#2
July 2nd, 2008, 05:38 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Checkbox & Textbox Used As Criteria For A Query
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 "hfrupn" wrote: 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
|
|
#3
July 3rd, 2008, 12:23 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Checkbox & Textbox Used As Criteria For A Query
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 "KARL DEWEY" wrote: 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 "hfrupn" wrote: 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
|
|
#4
July 3rd, 2008, 12:31 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Checkbox & Textbox Used As Criteria For A Query
Ok, say again when would you not want to see un-assigned records?
-- KARL DEWEY Build a little - Test a little "hfrupn" wrote: 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 "KARL DEWEY" wrote: 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 "hfrupn" wrote: 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
|
|
#5
July 3rd, 2008, 03:09 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Checkbox & Textbox Used As Criteria For A Query
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 "KARL DEWEY" wrote: Ok, say again when would you not want to see un-assigned records? -- KARL DEWEY Build a little - Test a little "hfrupn" wrote: 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 "KARL DEWEY" wrote: 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 "hfrupn" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
