multiple criteria to restrict output in report

[Guest]

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.

 

[Guest]

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

Try putting the criteria for each on the same row in the design view grid of
the query.
If you select all four criteria then it will pull data that matches all. If
a criteria is not selected then it is null and upon test for null kicks in
the wildcard.

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria:
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

Hi Karl, as you kindly suggested I put four of them all in one row in the
grid as followed:
[customer]:
Like Iif([Forms]![Selection Form]![cust] Is Null, "*", [Forms]![Selection
Form]![cust] )
[region]: Like Iif([Forms]![Selection Form]![reg] Is Null, "*",
[Forms]![Selection Form]![reg])
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu] )
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product] )

And when I make my selection in the selection form and try to run the
report, there is always an error says:
"[ query name ] is not a valid name. Make sure it does not include any
invalid character or punctuation and that it is not too long."

It is weird though because it was running ok before I made the changes and I
am sure the query name is not invalid since it is named as "query".

Any idea what went wrong?

Try putting the criteria for each on the same row in the design view grid of
the query.
If you select all four criteria then it will pull data that matches all. If
a criteria is not selected then it is null and upon test for null kicks in
the wildcard.

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria:
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.


:

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

Certian words are reserved in Access and should not be used as field names,
control names, or labels. Use something besides 'query' for the query name.

Have you by chance made one of the list boxes multi-select?

Hi Karl, as you kindly suggested I put four of them all in one row in the
grid as followed:
[customer]:
Like Iif([Forms]![Selection Form]![cust] Is Null, "*", [Forms]![Selection
Form]![cust] )
[region]: Like Iif([Forms]![Selection Form]![reg] Is Null, "*",
[Forms]![Selection Form]![reg])
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu] )
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product] )

And when I make my selection in the selection form and try to run the
report, there is always an error says:
"[ query name ] is not a valid name. Make sure it does not include any
invalid character or punctuation and that it is not too long."

It is weird though because it was running ok before I made the changes and I
am sure the query name is not invalid since it is named as "query".

Any idea what went wrong?

Try putting the criteria for each on the same row in the design view grid of
the query.
If you select all four criteria then it will pull data that matches all. If
a criteria is not selected then it is null and upon test for null kicks in
the wildcard.

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria:
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?





:

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.


:

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

Thanks Karl, but what do you mean by "making one of the list box
multi-select"?

Also, do i have to make one of the column in my select form as the " basis "
for other criteria? For example, if I enter [forms]![selection form]![cust]
for my [customer] column and the rest three columns use the iif statement
like u suggested, this will make it compulsory for a customer name to be
there and query result will be based on that customer and other criteria
specified about that customer.

what if I don't want to make any of the four the basis? will i be able to do
that by using iif statement for all four of them? for example, if i decide to
query around region, let say, japan, and then i want to see all info of a
certain BU in japan regardless which customer.

thanks Karl, i really appreciate your prompt and good comments.

Certian words are reserved in Access and should not be used as field names,
control names, or labels. Use something besides 'query' for the query name.

Have you by chance made one of the list boxes multi-select?

Hi Karl, as you kindly suggested I put four of them all in one row in the
grid as followed:
[customer]:
Like Iif([Forms]![Selection Form]![cust] Is Null, "*", [Forms]![Selection
Form]![cust] )
[region]: Like Iif([Forms]![Selection Form]![reg] Is Null, "*",
[Forms]![Selection Form]![reg])
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu] )
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product] )

And when I make my selection in the selection form and try to run the
report, there is always an error says:
"[ query name ] is not a valid name. Make sure it does not include any
invalid character or punctuation and that it is not too long."

It is weird though because it was running ok before I made the changes and I
am sure the query name is not invalid since it is named as "query".

Any idea what went wrong?

Try putting the criteria for each on the same row in the design view grid of
the query.
If you select all four criteria then it will pull data that matches all. If
a criteria is not selected then it is null and upon test for null kicks in
the wildcard.

:

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria:
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?





:

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.


:

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.

 

[Guest]

Karl, after retry it, I got it all worked out the way i want it to be.
Really thanks to you, I really appreciate your help

Certian words are reserved in Access and should not be used as field names,
control names, or labels. Use something besides 'query' for the query name.

Have you by chance made one of the list boxes multi-select?

Hi Karl, as you kindly suggested I put four of them all in one row in the
grid as followed:
[customer]:
Like Iif([Forms]![Selection Form]![cust] Is Null, "*", [Forms]![Selection
Form]![cust] )
[region]: Like Iif([Forms]![Selection Form]![reg] Is Null, "*",
[Forms]![Selection Form]![reg])
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu] )
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product] )

And when I make my selection in the selection form and try to run the
report, there is always an error says:
"[ query name ] is not a valid name. Make sure it does not include any
invalid character or punctuation and that it is not too long."

It is weird though because it was running ok before I made the changes and I
am sure the query name is not invalid since it is named as "query".

Any idea what went wrong?

Try putting the criteria for each on the same row in the design view grid of
the query.
If you select all four criteria then it will pull data that matches all. If
a criteria is not selected then it is null and upon test for null kicks in
the wildcard.

:

Thanks Karl, it works. But it is strange that when it works for a pair, the
other pair wouldn't work.
For example, using the code you suggested, I could query for the region on
the specific BU I enter; but when I try to do that for let say, to query BU
on the specific product, the region-BU pair would not work.
Here is how I position my criteria for the grid:

[customer] [region] [bu] [product]

Criteria:
------------------------------------------------------------------------------------------------
Or:
------------------------------------------------------------------------------------------------

Since there is not enough space, i will describe how the expressions are
position under each column for each row -
1st row
[customer]: [Forms]![Selection Form]![cust]
2nd row (after Or
[region]: [Forms]![Selection Form]![reg]
[bu]: Like Iif([Forms]![Selection Form]![bu] Is Null, "*",
[Forms]![Selection Form]![bu]
3rd row
[bu]: [Forms]![Selection Form]![bu]
[product]: Like Iif([Forms]![Selection Form]![product] Is Null, "*",
[Forms]![Selection Form]![product]
etc.
In order words, I want my form to be dynamic in the sense that I could
generate report for whatever combination of the four columns [customer],
[region], [bu] and [product] and also be able to produce report with all data
belong to that criteria if just one column is input with a criteria and all
other 3 are left blank.

any comments?





:

TESTED --- Try this --
Like Iif([Forms]![Selection Form]![bu] Is Null, "*", [Forms]![Selection
Form]![bu]

for bu column.


:

Hi, I have 4 columes where user can input their criteria from drop-down combo
box.
the names of columns are [customer], [region], [bu] and [product].

Here is what i want to achieve:
If I input a region from the [region] drop down in my form, i want to see
info regarding that particular region in the report;
if i go on to input a bu in the [bu] drop down, the result in the report
will be restricted to the entries that match the region selected and that bu
only.
For example, say I may have 20 entries returned if I choose Region#1, but if
I continue to put in a value for BU: Analysis Tool, I want to get a return of
all BU: Analysis Tool that belong to Region#1 which let say is only 5 counts.

My problem is when I do that, I get results of that region+result of that
BU(regardless of whether or not this BU belongs to that region specified), so
I may get 50 counts for instance.

Any idea what I may have done wrong? For my criteria grid, I put
[Forms]![Selection Form]![reg] for region column and [Forms]![Selection
Form]![bu] or [Forms]![Selection Form]![bu] is null for bu column.

Thanks.