Conditional Criteria

[Robin]

I have a form on which a UserID text box (vUserID) will always have the
current user's initials. On that form (_Main), I have a list box populated
from a query with ClientID, ClientName, ClientType and StaffID. (The User is
always one of the Staff) I would like an option group result (USER = 0 or
ALL = 1) to determine whether the list box shows ALL clients or just the ones
assigned to the USER. I wrote an Iif statement in the StaffID criteria as
follows: IIf (ogClientList = 0, Forms![_Main]![vUserID] , "") This works fine
to filter the list based on the UserID on the form, but when I choose ALL in
the option group the list box goes blank. I know this is because the filter
is now set to show only clients with no StaffID in the Query (due to the "" -
empty string in the "falsepart" of the expression...all Clients have a
StaffID) but I can't figure out how to make it say: if User is chosen in the
Option Group, filter but if All is chosen in the Option Group, don't filter
anything! I'm pretty good with Access and VBA so I should be able to figure
this out! Any help would be appreciated.

 

[Ken Snell \(MVP\)]

Try this as example for your RowSource query:

SELECT *
FROM Tablename
WHERE StaffID=Forms![_Main]![vUserID]
OR [ogClientList] = 1;

 

[Robin]

Thank you! Not only did this work (after I remembered to requery the list
box "AfterUpdate" of the option group!) but you taught me something I will
find useful in other queries... Although I understand the SQL way of doing
queries, I usually use the grid and I had always assumed the "or" parts of
the criteria were column specific and could not be used in conjunction with
multiple fields/controls. Thanks again!

Try this as example for your RowSource query:

SELECT *
FROM Tablename
WHERE StaffID=Forms![_Main]![vUserID]
OR [ogClientList] = 1;

--

Ken Snell
<MS ACCESS MVP>

I have a form on which a UserID text box (vUserID) will always have the
current user's initials. On that form (_Main), I have a list box
populated
from a query with ClientID, ClientName, ClientType and StaffID. (The User
is
always one of the Staff) I would like an option group result (USER = 0 or
ALL = 1) to determine whether the list box shows ALL clients or just the
ones
assigned to the USER. I wrote an Iif statement in the StaffID criteria as
follows: IIf (ogClientList = 0, Forms![_Main]![vUserID] , "") This works
fine
to filter the list based on the UserID on the form, but when I choose ALL
in
the option group the list box goes blank. I know this is because the
filter
is now set to show only clients with no StaffID in the Query (due to the
"" -
empty string in the "falsepart" of the expression...all Clients have a
StaffID) but I can't figure out how to make it say: if User is chosen in
the
Option Group, filter but if All is chosen in the Option Group, don't
filter
anything! I'm pretty good with Access and VBA so I should be able to
figure
this out! Any help would be appreciated.