query criteria entered via a form

G

Guest

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
 
G

Guest

What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR
 
G

Guest

Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

Ofer said:
What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


Lori said:
I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
 
G

Guest

can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

Ofer said:
What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


Lori said:
I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Here's the SQL - I used every possible combination - all 3 criteria with
values, each by itself, and each combination of only 2 criteria being
selected. Also, I was mistaken earlier - the only way the result is correct
is when I pick only one criteria from the form. Thanks for your help!

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));


Ofer said:
can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

Ofer said:
What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


:

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Try if this give you the resault you are looking for

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (tbl_Vendor.Chain_Suffix=[Forms]![frm_rpt_test]![Chain_Suffix] Or
[Forms]![frm_rpt_test]![Chain_Suffix] Is Null) AND
(tbl_Vendor.Region_Code=[Forms]![frm_rpt_test]![Region_Code] Or
[Forms]![frm_rpt_test]![Region_Code] Is Null) AND
(tbl_Vendor.Status_Code=[Forms]![frm_rpt_test]![Status_Code] Or
[Forms]![frm_rpt_test]![Status_Code] Is Null)

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Here's the SQL - I used every possible combination - all 3 criteria with
values, each by itself, and each combination of only 2 criteria being
selected. Also, I was mistaken earlier - the only way the result is correct
is when I pick only one criteria from the form. Thanks for your help!

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));


Ofer said:
can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

:

What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


:

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You might want to look at solution I post for Freeda on 12/2/05 many-to-many
relationship. Database Design

It has a form for both criteria and checkbox for And / Or to set function of
the criteria.

Lori said:
Here's the SQL - I used every possible combination - all 3 criteria with
values, each by itself, and each combination of only 2 criteria being
selected. Also, I was mistaken earlier - the only way the result is correct
is when I pick only one criteria from the form. Thanks for your help!

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));


Ofer said:
can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

:

What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


:

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It worked!!!! Thank-you so much for your help and patience!!! Lori

Ofer said:
Try if this give you the resault you are looking for

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (tbl_Vendor.Chain_Suffix=[Forms]![frm_rpt_test]![Chain_Suffix] Or
[Forms]![frm_rpt_test]![Chain_Suffix] Is Null) AND
(tbl_Vendor.Region_Code=[Forms]![frm_rpt_test]![Region_Code] Or
[Forms]![frm_rpt_test]![Region_Code] Is Null) AND
(tbl_Vendor.Status_Code=[Forms]![frm_rpt_test]![Status_Code] Or
[Forms]![frm_rpt_test]![Status_Code] Is Null)

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Here's the SQL - I used every possible combination - all 3 criteria with
values, each by itself, and each combination of only 2 criteria being
selected. Also, I was mistaken earlier - the only way the result is correct
is when I pick only one criteria from the form. Thanks for your help!

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));


Ofer said:
can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


:

Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

:

What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


:

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Your welcome, good luck

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
It worked!!!! Thank-you so much for your help and patience!!! Lori

Ofer said:
Try if this give you the resault you are looking for

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (tbl_Vendor.Chain_Suffix=[Forms]![frm_rpt_test]![Chain_Suffix] Or
[Forms]![frm_rpt_test]![Chain_Suffix] Is Null) AND
(tbl_Vendor.Region_Code=[Forms]![frm_rpt_test]![Region_Code] Or
[Forms]![frm_rpt_test]![Region_Code] Is Null) AND
(tbl_Vendor.Status_Code=[Forms]![frm_rpt_test]![Status_Code] Or
[Forms]![frm_rpt_test]![Status_Code] Is Null)

--
\\// Live Long and Prosper \\//
BS"D


Lori said:
Here's the SQL - I used every possible combination - all 3 criteria with
values, each by itself, and each combination of only 2 criteria being
selected. Also, I was mistaken earlier - the only way the result is correct
is when I pick only one criteria from the form. Thanks for your help!

SELECT tbl_Vendor.Vendor_Nbr, tbl_Vendor.Vendor_Name,
tbl_Vendor.Chain_Suffix, tbl_Vendor.Region_Code, tbl_Vendor.Status_Code
FROM tbl_Vendor
WHERE (((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code]) AND
((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code])) OR
(((tbl_Vendor.Chain_Suffix)=[Forms]![frm_rpt_test]![Chain_Suffix])) OR
(((tbl_Vendor.Region_Code)=[Forms]![frm_rpt_test]![Region_Code])) OR
(((tbl_Vendor.Status_Code)=[Forms]![frm_rpt_test]![Status_Code]));


:

can you post the SQL you have now?

--
\\// Live Long and Prosper \\//
BS"D


:

Hi,

I got the two criteria to work. I have since added a third - I can get it
to work if I fill in only one field or all but not if I do 2 out of the
three. I need some way to represent a value not being selected from the 3rd
field. Any suggestions?

:

What happen when you run only "2" from the Region field, without the status?

If it run correctly, it could be that the criteria you are looking for is OR
and not AND as you have now.
If that the case move the Region criteria one box under, or in SQL view
change the AND to OR


--
\\// Live Long and Prosper \\//
BS"D


:

I am using Access 2002 and created an unbound form with a couple fields to be
used for search criteria to be selected on the fly to drive a query. I have
a field called Region and one called Status. I entered the form fields in
the query criteria but can only get the results to work using one selection
criteria at a time. How can I make the query run if both selection criteria
have a value selected from it?

For example, if I select "active" from the status drop-down field on the
form, the query runs correctly. But if I select "active" from the status
field and "2" from the Region field - the results are inaccurate.

Here is what I have in the query criteria: Under the Region field -
[Forms]![frm_rpt_test]![Region_Code] and under the Status field -
[Forms]![frm_rpt_test]![Status_Code]. I know I am not finding the right
combination or rows to place this data in the query design. Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Changing query criteria to supply a form 3
Use In() Expression as Query criteria with guts from Function 1
Criteria in a Querie 8
Complex Criteria Parameter Query 2
Dynamic Query Criteria and returning all records (*) 1
criteria for date field using iif() 1
criteria failure 7

Top