Dynamic query using no, some, or 2 CRITERIA fields

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am wondering how to use a SINGLE query and utilize no, one, or both of the
criteria depening on my login criteria.

The 2 criteria are:
[Forms]![frmLogin].[cboDivision]
[Forms]![frmLogin].[cboManager]

Now, depending on someone's login criteria, there will be different levels
of hierarchy.

1. One user may be only allowed to view reports on the "Manager level"... in
this case, I need to execute the query with BOTH criteria (cboDivision) and
(cboManager) included.

2. Another user could be allowed to view reports on the "Division level"...
in this case, he should see all branches that are branches that are part of
his/her division. Hence, I only want the query to include the the
(cboDivision) criteria.

3. Finally, the most senior level user should be able to view all records,
so I do NOT need to include any criteria.


Yes, I simply could create 3 queries that will list neither, both or only
the cboDivision criteria. And depending on the login, I would execute the
proper one. However, I don't want that... Copying the SQL code into VBA
and executing the proper one via CASE SELECT would be just like creating 3
queries.

So, is there any way to have a SINGLE query and make it somehow dynamic (in
respect to what criteria needs to be included/excluded)?

Tom
 
1) Use VBA to look up the access level of the user. The two criteria you
have listed, Division and Manager, aren't sufficient (by your description)
to make this decision. You'll need to look up what that person is allowed to
see. Once you know that, use VBA again to fill in 2 textboxes (txtDivision
and txtManager) on your form (set their Visible property to No so they won't
be seen by the user). If the user should be filtered by Manager, fill in
that box; if the user should be filtered by Division, fill in that box; if
the user should be filtered by both, fill in both boxes; if the user should
be filtered by neither, leave (or set) both boxes to Null.

2) In the query, change the criteria to point to these two textboxes instead
of the combo boxes. Also you'll need to add a statement to the criteria to
show all records of that type if the associated textbox is Null.

Example:
=[Forms]![frmLogin].[txtDivision] Or [Forms]![frmLogin].[txtDivision] Is
Null

As you know, for an Or statement, only one side of the statement needs to be
true. If there is a value in the textbox, you'll get

= TheValue Or False

"TheValue Or False" evaluates to TheValue. If there is not a value in the
textbox you'll get

=Null Or True

"Null Or True" evaluates to True. If you use True as criteria, you get all
records returned.
 
Wayne,

thanks for the info... I'll check it out later on at work.

Thanks so much already in advance,
Tom


Wayne Morgan said:
1) Use VBA to look up the access level of the user. The two criteria you
have listed, Division and Manager, aren't sufficient (by your description)
to make this decision. You'll need to look up what that person is allowed
to see. Once you know that, use VBA again to fill in 2 textboxes
(txtDivision and txtManager) on your form (set their Visible property to
No so they won't be seen by the user). If the user should be filtered by
Manager, fill in that box; if the user should be filtered by Division,
fill in that box; if the user should be filtered by both, fill in both
boxes; if the user should be filtered by neither, leave (or set) both
boxes to Null.

2) In the query, change the criteria to point to these two textboxes
instead of the combo boxes. Also you'll need to add a statement to the
criteria to show all records of that type if the associated textbox is
Null.

Example:
=[Forms]![frmLogin].[txtDivision] Or [Forms]![frmLogin].[txtDivision] Is
Null

As you know, for an Or statement, only one side of the statement needs to
be true. If there is a value in the textbox, you'll get

= TheValue Or False

"TheValue Or False" evaluates to TheValue. If there is not a value in the
textbox you'll get

=Null Or True

"Null Or True" evaluates to True. If you use True as criteria, you get all
records returned.

--
Wayne Morgan
MS Access MVP


Tom said:
I am wondering how to use a SINGLE query and utilize no, one, or both of
the criteria depening on my login criteria.

The 2 criteria are:
[Forms]![frmLogin].[cboDivision]
[Forms]![frmLogin].[cboManager]

Now, depending on someone's login criteria, there will be different
levels of hierarchy.

1. One user may be only allowed to view reports on the "Manager level"...
in this case, I need to execute the query with BOTH criteria
(cboDivision) and (cboManager) included.

2. Another user could be allowed to view reports on the "Division
level"... in this case, he should see all branches that are branches that
are part of his/her division. Hence, I only want the query to include
the the (cboDivision) criteria.

3. Finally, the most senior level user should be able to view all
records, so I do NOT need to include any criteria.


Yes, I simply could create 3 queries that will list neither, both or only
the cboDivision criteria. And depending on the login, I would execute
the proper one. However, I don't want that... Copying the SQL code into
VBA and executing the proper one via CASE SELECT would be just like
creating 3 queries.

So, is there any way to have a SINGLE query and make it somehow dynamic
(in respect to what criteria needs to be included/excluded)?

Tom
Click to expand...
Click to expand...
 
Wayne,

I actually got this to work w/ the 2 combos... w/o your idea I wouldn't
have been able to get this to work... particularly the 2nd portion of "...
IS NULL" in the criteria field.

Thousand thanks!

Tom


Wayne Morgan said:
1) Use VBA to look up the access level of the user. The two criteria you
have listed, Division and Manager, aren't sufficient (by your description)
to make this decision. You'll need to look up what that person is allowed
to see. Once you know that, use VBA again to fill in 2 textboxes
(txtDivision and txtManager) on your form (set their Visible property to
No so they won't be seen by the user). If the user should be filtered by
Manager, fill in that box; if the user should be filtered by Division,
fill in that box; if the user should be filtered by both, fill in both
boxes; if the user should be filtered by neither, leave (or set) both
boxes to Null.

2) In the query, change the criteria to point to these two textboxes
instead of the combo boxes. Also you'll need to add a statement to the
criteria to show all records of that type if the associated textbox is
Null.

Example:
=[Forms]![frmLogin].[txtDivision] Or [Forms]![frmLogin].[txtDivision] Is
Null

As you know, for an Or statement, only one side of the statement needs to
be true. If there is a value in the textbox, you'll get

= TheValue Or False

"TheValue Or False" evaluates to TheValue. If there is not a value in the
textbox you'll get

=Null Or True

"Null Or True" evaluates to True. If you use True as criteria, you get all
records returned.

--
Wayne Morgan
MS Access MVP


Tom said:
I am wondering how to use a SINGLE query and utilize no, one, or both of
the criteria depening on my login criteria.

The 2 criteria are:
[Forms]![frmLogin].[cboDivision]
[Forms]![frmLogin].[cboManager]

Now, depending on someone's login criteria, there will be different
levels of hierarchy.

1. One user may be only allowed to view reports on the "Manager level"...
in this case, I need to execute the query with BOTH criteria
(cboDivision) and (cboManager) included.

2. Another user could be allowed to view reports on the "Division
level"... in this case, he should see all branches that are branches that
are part of his/her division. Hence, I only want the query to include
the the (cboDivision) criteria.

3. Finally, the most senior level user should be able to view all
records, so I do NOT need to include any criteria.


Yes, I simply could create 3 queries that will list neither, both or only
the cboDivision criteria. And depending on the login, I would execute
the proper one. However, I don't want that... Copying the SQL code into
VBA and executing the proper one via CASE SELECT would be just like
creating 3 queries.

So, is there any way to have a SINGLE query and make it somehow dynamic
(in respect to what criteria needs to be included/excluded)?

Tom
Click to expand...
Click to expand...
 
Back
Top