match multiple fields

[Guest]

Please help
I am trying to create a report that will use a filter query. when I start
the report, it brings up a form with various boxes for filter criteria. If a
box is not filled in then all the records are still included. Once one or
more boxes are filled in the report is filtered to that criteria. So far so
good. Each record can be associated with up to 3 different products or
tests. So here is the question:
How can I include a record if either product1, product2 or product3 match
"[Forms]![Test Report Files]![ProductID]"?
I also want to include all the records if this form field is null. I have
tried something like:
IIf(IsNull([Forms]![Test Report Files]![productID]),[Product1],[Forms]![Test
Report Files]![ProductID])

Any help would be greatly appreciated.
Thanks in advance, Jack

 

[Michel Walsh]

Hi,



.... WHERE Forms![Test Report Files]![ProductID] IN (product1, product2,
product3)




You should avoid spaces in names (use description and caption if you like to
get a space or nicely written label for "presentation", but for names, it is
far preferable to not have spaces in them).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for the response. However, I am unfamiliar with the WHERE clause.
It looks like it should work. I just don't know where to put this statement.
Does it go in the criteria field right after the IIF () statement? should I
use a comma before the WHERE statement? I have tried it several ways and
keep getting "invalid syntax" error messages. Not sure what I am missing

Hi,



.... WHERE Forms![Test Report Files]![ProductID] IN (product1, product2,
product3)




You should avoid spaces in names (use description and caption if you like to
get a space or nicely written label for "presentation", but for names, it is
far preferable to not have spaces in them).



Hoping it may help,
Vanderghast, Access MVP

Please help
I am trying to create a report that will use a filter query. when I start
the report, it brings up a form with various boxes for filter criteria.
If a
box is not filled in then all the records are still included. Once one or
more boxes are filled in the report is filtered to that criteria. So far
so
good. Each record can be associated with up to 3 different products or
tests. So here is the question:
How can I include a record if either product1, product2 or product3 match
"[Forms]![Test Report Files]![ProductID]"?
I also want to include all the records if this form field is null. I have
tried something like:
IIf(IsNull([Forms]![Test Report
Files]![productID]),[Product1],[Forms]![Test
Report Files]![ProductID])

Any help would be greatly appreciated.
Thanks in advance, Jack

 

[Michel Walsh]

Hi,

If you use the query designer, type the parameter
(Forms!FormName!ControlName) in the first line, and, in the Criteria line,
type

IN(product1, product2, product3)


Alternatively, switch from the design view to the SQL view. If there is
already a WHERE clause, add the new one (probably with an AND ):


SELECT ... FROM.... WHERE ..... AND Forms!FormName!ControlName IN( f1, f2,
f3) ;


If there is not WHERE clause already there, add it ( but before the ending
semi colon).


SELECT ... FROM ... WHERE Forms!FormName!ControlName IN(f1, f2, f3) ;



Sure, to run the query or to use it as rowsource (for a list of a combo box,
or otherwise), the mentioned form should be open.


Hoping it may help,
Vanderghast, Access MVP

Thank you for the response. However, I am unfamiliar with the WHERE
clause.
It looks like it should work. I just don't know where to put this
statement.
Does it go in the criteria field right after the IIF () statement? should
I
use a comma before the WHERE statement? I have tried it several ways and
keep getting "invalid syntax" error messages. Not sure what I am missing

Hi,



.... WHERE Forms![Test Report Files]![ProductID] IN (product1, product2,
product3)




You should avoid spaces in names (use description and caption if you like
to
get a space or nicely written label for "presentation", but for names, it
is
far preferable to not have spaces in them).



Hoping it may help,
Vanderghast, Access MVP

Please help
I am trying to create a report that will use a filter query. when I
start
the report, it brings up a form with various boxes for filter criteria.
If a
box is not filled in then all the records are still included. Once one
or
more boxes are filled in the report is filtered to that criteria. So
far
so
good. Each record can be associated with up to 3 different products or
tests. So here is the question:
How can I include a record if either product1, product2 or product3
match
"[Forms]![Test Report Files]![ProductID]"?
I also want to include all the records if this form field is null. I
have
tried something like:
IIf(IsNull([Forms]![Test Report
Files]![productID]),[Product1],[Forms]![Test
Report Files]![ProductID])

Any help would be greatly appreciated.
Thanks in advance, Jack