Multiple criteria in a parameter query

[Stacy]

So, I know this is probably a simple issue, but after banging my head
against the wall and reading almsot every newsgroup article I came
across, I still just can't seem to get this right.

I have a query that runs from a form.
I have set up criteria on the form so the users can select the exact
records they are looking for.

So, for example I have:
RegID
RegDescription
RegOwner
RegOwnerArea
Function
FunctionOwner

I have selected fields on the form (combo box).
Here's how it's supposed to work...
If RegOwner is selected, run query based on RegOwner criteria
selected...
If RegOwner is selected, and RegFunction is selected... run the query
based on both selections.

So, in the criteria field for all 5 fields, I have the following
statement:
Like IIf(Isnull([Forms]![FormName]![FormComboBoxFieldName]),"*",
[Forms]![FormName]![FormComboBoxFieldName]).

It works great when I tested it on the first field.
However, once I added that same criteria to the second field, it's not
giving me the correct records.
It only gives me the correct records if all combo boxes are selected.

Can anyone tell me what I am missing?
Thanks, it's very much appreciated!
Stacy

 

[KARL DEWEY]

You have to place the criteria on a different row in the design view grid for
it to OR. When placed on the same row all criteria must be true.
I had an occasion to need AND sometime and OR others so I added a checkbox
to the form for And/Or. Then in the query And'd the And/Or with other
criteria, using 5 rows for 4 criteria. One row had all criteria plus the
And/Or set for And. The other 4 rows had a single criteria and the And/Or
set for Or.

 

[Stacy]

You have to place the criteria on a different row in the design view grid for
it to OR.  When placed on the same row all criteria must be true.
I had an occasion to need AND sometime and OR others so I added a checkbox
to the form for And/Or.  Then in the query And'd the And/Or with other
criteria, using 5 rows for 4 criteria.   One row had all criteria plus the
And/Or set for And.  The other 4 rows had a single criteria and the And/Or
set for Or.
--
KARL DEWEY
Build a little - Test a little

So, I know this is probably a simple issue, but after banging my head
against the wall and reading almsot every newsgroup article I came
across, I still just can't seem to get this right.

I have a query that runs from a form.
I have set up criteria on the form so the users can select the exact
records they are looking for.

So, for example I have:
RegID
RegDescription
RegOwner
RegOwnerArea
Function
FunctionOwner

I have selected fields on the form (combo box).
Here's how it's supposed to work...
If RegOwner is selected, run query based on RegOwner criteria
selected...
If RegOwner is selected, and RegFunction is selected... run the query
based on both selections.

So, in the criteria field for all 5 fields, I have the following
statement:
Like IIf(Isnull([Forms]![FormName]![FormComboBoxFieldName]),"*",
[Forms]![FormName]![FormComboBoxFieldName]).

It works great when I tested it on the first field.
However, once I added that same criteria to the second field, it's not
giving me the correct records.
It only gives me the correct records if all combo boxes are selected.

Can anyone tell me what I am missing?
Thanks, it's very much appreciated!
Stacy- Hide quoted text -

- Show quoted text -

That didn't work quite the way I had hoped, but it's a start.
I'll have to play around with the And/Or part of it since I really
don't want to limit them to one selection.

Thanks!!!!

 

[KARL DEWEY]

I did not read your post close enough.
Use this --
Like [Forms]![FormName]![FormComboBoxFieldName]&"*" OR
[Forms]![FormName]![FormComboBoxFieldName] Is Null

--
KARL DEWEY
Build a little - Test a little

You have to place the criteria on a different row in the design view grid for
it to OR. When placed on the same row all criteria must be true.
I had an occasion to need AND sometime and OR others so I added a checkbox
to the form for And/Or. Then in the query And'd the And/Or with other
criteria, using 5 rows for 4 criteria. One row had all criteria plus the
And/Or set for And. The other 4 rows had a single criteria and the And/Or
set for Or.
--
KARL DEWEY
Build a little - Test a little

So, I know this is probably a simple issue, but after banging my head
against the wall and reading almsot every newsgroup article I came
across, I still just can't seem to get this right.

I have a query that runs from a form.
I have set up criteria on the form so the users can select the exact
records they are looking for.

So, for example I have:
RegID
RegDescription
RegOwner
RegOwnerArea
Function
FunctionOwner

I have selected fields on the form (combo box).
Here's how it's supposed to work...
If RegOwner is selected, run query based on RegOwner criteria
selected...
If RegOwner is selected, and RegFunction is selected... run the query
based on both selections.

So, in the criteria field for all 5 fields, I have the following
statement:
Like IIf(Isnull([Forms]![FormName]![FormComboBoxFieldName]),"*",
[Forms]![FormName]![FormComboBoxFieldName]).

It works great when I tested it on the first field.
However, once I added that same criteria to the second field, it's not
giving me the correct records.
It only gives me the correct records if all combo boxes are selected.

Can anyone tell me what I am missing?
Thanks, it's very much appreciated!
Stacy- Hide quoted text -

- Show quoted text -

That didn't work quite the way I had hoped, but it's a start.
I'll have to play around with the And/Or part of it since I really
don't want to limit them to one selection.

Thanks!!!!

 

[Lars Brownie]

Take a look at http://www.mvps.org/access/queries/qry0001.htm.

When using these criteria on more fields you have to add them to the query
in one action.
If you first add a criteria for one field, save the query, open it again and
add a criteria for another field, Access does not know how to interpret your
criteria and it won't give you the desired result. Watch how the query will
look when you open it again.

Lars

"Stacy" <[email protected]> schreef in bericht

You have to place the criteria on a different row in the design view grid
for
it to OR. When placed on the same row all criteria must be true.
I had an occasion to need AND sometime and OR others so I added a checkbox
to the form for And/Or. Then in the query And'd the And/Or with other
criteria, using 5 rows for 4 criteria. One row had all criteria plus the
And/Or set for And. The other 4 rows had a single criteria and the And/Or
set for Or.
--
KARL DEWEY
Build a little - Test a little

So, I know this is probably a simple issue, but after banging my head
against the wall and reading almsot every newsgroup article I came
across, I still just can't seem to get this right.

I have a query that runs from a form.
I have set up criteria on the form so the users can select the exact
records they are looking for.

So, for example I have:
RegID
RegDescription
RegOwner
RegOwnerArea
Function
FunctionOwner

I have selected fields on the form (combo box).
Here's how it's supposed to work...
If RegOwner is selected, run query based on RegOwner criteria
selected...
If RegOwner is selected, and RegFunction is selected... run the query
based on both selections.

So, in the criteria field for all 5 fields, I have the following
statement:
Like IIf(Isnull([Forms]![FormName]![FormComboBoxFieldName]),"*",
[Forms]![FormName]![FormComboBoxFieldName]).

It works great when I tested it on the first field.
However, once I added that same criteria to the second field, it's not
giving me the correct records.
It only gives me the correct records if all combo boxes are selected.

Can anyone tell me what I am missing?
Thanks, it's very much appreciated!
Stacy- Hide quoted text -

- Show quoted text -

That didn't work quite the way I had hoped, but it's a start.
I'll have to play around with the And/Or part of it since I really
don't want to limit them to one selection.

Thanks!!!!

 

[Stacy]

Take a look athttp://www.mvps.org/access/queries/qry0001.htm.

When using these criteria on more fields you have to add them to the query
in one action.
If you first add a criteria for one field, save the query, open it again and
add a criteria for another field, Access does not know how to interpret your
criteria and it won't give you the desired result. Watch how the query will
look when you open it again.

Lars

"Stacy" <[email protected]> schreef in bericht

You have to place the criteria on a different row in the design view grid
for
it to OR. When placed on the same row all criteria must be true.
I had an occasion to need AND sometime and OR others so I added a checkbox
to the form for And/Or. Then in the query And'd the And/Or with other
criteria, using 5 rows for 4 criteria. One row had all criteria plus the
And/Or set for And. The other 4 rows had a single criteria and the And/Or
set for Or.

So, I know this is probably a simple issue, but after banging my head
against the wall and reading almsot every newsgroup article I came
across, I still just can't seem to get this right.
I have a query that runs from a form.
I have set up criteria on the form so the users can select the exact
records they are looking for.
So, for example I have:
RegID
RegDescription
RegOwner
RegOwnerArea
Function
FunctionOwner
I have selected fields on the form (combo box).
Here's how it's supposed to work...
If RegOwner is selected, run query based on RegOwner criteria
selected...
If RegOwner is selected, and RegFunction is selected... run the query
based on both selections.
So, in the criteria field for all 5 fields, I have the following
statement:
Like IIf(Isnull([Forms]![FormName]![FormComboBoxFieldName]),"*",
[Forms]![FormName]![FormComboBoxFieldName]).
It works great when I tested it on the first field.
However, once I added that same criteria to the second field, it's not
giving me the correct records.
It only gives me the correct records if all combo boxes are selected.
Can anyone tell me what I am missing?
Thanks, it's very much appreciated!
Stacy- Hide quoted text -

- Show quoted text -

That didn't work quite the way I had hoped, but it's a start.
I'll have to play around with the And/Or part of it since I really
don't want to limit them to one selection.

Thanks!!!!- Hide quoted text -

- Show quoted text -

YAY!
I think it's finally working....

Thank you both so much!