criteria in a query

  • Thread starter Thread starter Ulcom
  • Start date Start date
U

Ulcom

Hi
I have a query named QR TEST with ten fields and I need to give a criteria
to one of the field. This field is named "NUMBER"
I have 2 different forms let say Form A and Form B. Each one of them
contain a field named "NUMBER"
When I open Form A, I want, when I click a button that the criteria for the
field "NUMBER" in the query QR TEST change for
[Forms]![Form A]![NUMBER]
And when I open Form B, when I click a button the criteria for the field
"NUMBER" in the query QR TEST change for
[Forms]![Form B]![NUMBER].

I Have tried to put both criterias in the query like [Forms]![Form
A]![NUMBER] OR [Forms]![Form B]![NUMBER]
but when I open the Query a box open asking me NUMBER then if I click OK
then the query open with records.
So how can I input criteria to a query from a form
thanks
 
I'm not an Access expert and I may be interpreting your question incorrectly;
but, it sounds like you're expectation of the query is not valid. If a query
has a variable in the criteria, the query engine is going to resolve that
variable before it retrieves the data...

take a simple criteria like...
where sales = $someValue OR sales = $someOtherValue

$someValue has to have a value (say 10)
and
$someOtherValue has to also have a value (say 15)

this way the query will resolve to
where sales = 10 OR sales = 15
and the you retrieve all the salesmen with sales of 10 and you'll also
retrieve all the salesmen with sales of 15

I may be interpreting you question incorrectly; but, it sounds like you're
expecting the criteria to resolve to...

where sales = 10 (if the person entered 10 on a certain form)
in which case you'll retrieve all the salesmen with sales of 10 and you'll
retrieve nothing else.

or have a completely different criteria resolve to....
where sales = 15 (if the person enters 15 on a different form)
in which case you'll retrieve all the salesmen with sales of 15 and you'll
retrieve nothing else.

......

now using your form data, if you have criteria of
where sales=[forms]![form a]![amt] OR sales=[forms]![form b]![amt]

in order for you to run this query without getting that basic variable
prompt, you will need to have both Form A and Form B open with their
respective fields entered (and I don't get the sense that this is what is
intended)

a rough rule of thumb is if you're going to have a form for supplying
criteria information, there should be an explicit query for each form. So a
long-winded way to say, that without coding....

Form 1 is going to need it's own query
and
Form 2 is going to need it's own query, even if the selections is exactly
the same.

Otherwise, you'll need to write some VB Script to dynamically generate your
SQL statement which is a whole other conversation.

--Hise

Ulcom said:
Hi
I have a query named QR TEST with ten fields and I need to give a criteria
to one of the field. This field is named "NUMBER"
I have 2 different forms let say Form A and Form B. Each one of them
contain a field named "NUMBER"
When I open Form A, I want, when I click a button that the criteria for the
field "NUMBER" in the query QR TEST change for
[Forms]![Form A]![NUMBER]
And when I open Form B, when I click a button the criteria for the field
"NUMBER" in the query QR TEST change for
[Forms]![Form B]![NUMBER].

I Have tried to put both criterias in the query like [Forms]![Form
A]![NUMBER] OR [Forms]![Form B]![NUMBER]
but when I open the Query a box open asking me NUMBER then if I click OK
then the query open with records.
So how can I input criteria to a query from a form
thanks
Click to expand...
 
Back
Top