Permanent Query Criteria in a Form??

[cassius]

I use several similar queries that require the same criteria in the same field (jobs). I exclude jobs in each query's criteria with <>"job1" AND <>"job2" AND <>"job3". Occasionally I have to add another job to exclude. Is it possible (you know I'm new here) to create a control somewhere so I only have to make changes to the control and not each query whenever I add a new job to exclude?? Thanks for any assistance/advice

 

[bjackson]

is it possible to add a field to your jobs table say "ExcludeJob"
data type yes/no,
then you could use 1 query to return all jobs who's "ExcludeJob" =false
You could design a popup form to allow you to edit the "ExcludeJob" field
from your main form and set 1 or all records to whatever you need

 

[cassius]

is it possible to add a field to your jobs table say "ExcludeJob"
data type yes/no,
then you could use 1 query to return all jobs who's "ExcludeJob" =false
You could design a popup form to allow you to edit the "ExcludeJob" field
from your main form and set 1 or all records to whatever you need

Possible but not practical; I import two tables with jobs (thousands) every couple of days (static updates). I wish not to modify these tables.

I feel there must be some version of QBF that will work for me but just haven't hit upon it yet.
Thanks!

 

[DALeffler]

This is not clean, but'll 'prolly work...

Add 4 or 5 unbound check box controls (or however many "jobn"s you wish to include/exclude) to your form.

Set the default value for each check box True for the jobs you'd normally expect to exclude & False for the jobs you don't normally exclude.

In the query for the form, for each check box you added to your form, a Query design grid column should be added having:

Field: Iff([MyTable].[Jobs] = "jobn" And [Forms]![MyForm]![MyChkBxJobn] = Yes, False, True)
Show: Not Shown
Criteria: True

Make sure that each new query column you have has the criteria value "True" in the same row across the Query design grid.

Add form requerys any time one of the check boxes is clicked.

lemme know if that works,

Doug.

 

[cassius]

This is not clean, but'll 'prolly work...

Add 4 or 5 unbound check box controls (or however many "jobn"s you wish to include/exclude) to your form.

Set the default value for each check box True for the jobs you'd normally expect to exclude & False for the jobs you don't normally exclude.

In the query for the form, for each check box you added to your form, a Query design grid column should be added having:

Field: Iff([MyTable].[Jobs] = "jobn" And [Forms]![MyForm]![MyChkBxJobn] = Yes, False, True)
Show: Not Shown
Criteria: True

Make sure that each new query column you have has the criteria value "True" in the same row across the Query design grid.

Add form requerys any time one of the check boxes is clicked.

lemme know if that works,

Doug.

Hi Doug,
My current criteria to exclude jobs [Task IDs] in six queries is the expression:

<>"02-0859" And <>"02-0860" And <>"02-0652" And <>"02-0827" And <>"03-0801" And <>"03-U218" And <>"03-U763" And <>"03-U938" And <>"03-U139 "

I am ready to exclude more jobs: 04-0601, 04-0606, etc.
Current modus is to open each query in design view and add addtional jobs to the expression.

What I am trying to find is:
1) A control where I can write the above expression or equivalent, add additional jobs when required, and only have to modify the expression once in the control.
2) The code or expression to put in each of the six queries that "grabs" the above expression from the control and inserts it in the criteria in the jobs field .

I am trying to avoid having to keep going back to the two tables that hold the jobs and the 6 queries that use the same criteria to modify them as I exclude new jobs.

Thanks for your assistance and patience

 

[DALeffler]

If you're asking whether or not there is a control you can put on your form that'll parse it's value into a query's criteria expression, then no - at least not that I know of.

The only other way I know to do what you want is to create an SQL Where clause and run the generated SQL statement. Performance wise, that can get expensive (at least in Acc '97).

Doug.

(edit: sorry, added "expression" in first sentence...)

 

[bjackson]

I would create a table to store the excluded job nos and join the tables using
the job no.then run a query searching for unmatched data.
No need to add any criteria just add the excluded jobno to your table
If the jobno appears in your new table then it wont appear in your query
then design a form to allow you to easily add or delete a job no

 

[cassius]

I would create a table to store the excluded job nos and join the tables using
the job no.then run a query searching for unmatched data.
No need to add any criteria just add the excluded jobno to your table
If the jobno appears in your new table then it wont appear in your query
then design a form to allow you to easily add or delete a job no

Thanks! This is what I was looking for. I could never get the expression quite right in my existing queries to exclude jobnos and I've never paid close attention to the options in the query wizard.