Combo Box Selection in Query Criteria Line

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi.

I have a form with five combo boxes, each have a row
source that pulls data from a table with the same name as
the combo box.

Org (10 to choose from) pulls from table named "Org"
BU (4 to choose from) pulls from talble named "BU"
IOO (6 to choose from) pulls from table named "IOO"
IBO (100 to choose from) pulls from table named "IBO"
Item (100,000 to choose from) pulls from table "ALL"

I have a command button that runs a query
named "Output_Detail" on click of the command button. The
query has the following in the criteria line for these
fields:

Org field: [Forms]![EOData]![Org]
BU field: [Forms]![EOData]![BU]
IOO field: [Forms]![EOData]![IOO]
IBO field: [Forms]![EOData]![IBO]
Item field: [Forms]![EOData]![Item]

The problem is this: if only the org is selected, the
query is interpreting the other fields as having some
value that is not found in my table (which is
named "ALL"), and the query returns no records. I added a
blank record to the BU, IOO, and IBO tables hoping that if
nothing was selected in these combo boxes, that the field
criteria in my query would default to blanks (as if there
were no filters for those fields), and would then give me
all records for the org selected (for example, if F01 was
selected for org, the only criteria/filter in my query
would be to pull all records from my "ALL" table that have
F01 as the org).

I found a "default" field in my combo box data properties.
Is there a value I can enter here so that if no existing
field is selected in the combo box, that this default
value would be read into my query criteria field?
 
Steve said:
I have a form with five combo boxes, each have a row
source that pulls data from a table with the same name as
the combo box.

Org (10 to choose from) pulls from table named "Org"
BU (4 to choose from) pulls from talble named "BU"
IOO (6 to choose from) pulls from table named "IOO"
IBO (100 to choose from) pulls from table named "IBO"
Item (100,000 to choose from) pulls from table "ALL"

I have a command button that runs a query
named "Output_Detail" on click of the command button. The
query has the following in the criteria line for these
fields:

Org field: [Forms]![EOData]![Org]
BU field: [Forms]![EOData]![BU]
IOO field: [Forms]![EOData]![IOO]
IBO field: [Forms]![EOData]![IBO]
Item field: [Forms]![EOData]![Item]

The problem is this: if only the org is selected, the
query is interpreting the other fields as having some
value that is not found in my table (which is
named "ALL"), and the query returns no records. I added a
blank record to the BU, IOO, and IBO tables hoping that if
nothing was selected in these combo boxes, that the field
criteria in my query would default to blanks (as if there
were no filters for those fields), and would then give me
all records for the org selected (for example, if F01 was
selected for org, the only criteria/filter in my query
would be to pull all records from my "ALL" table that have
F01 as the org).
Click to expand...


You need to specify the criteria so that it returns True
when a combo box has not had a value selected. Change each
criteria to look like:

Org field:
[Forms]![EOData]![Org] Or [Forms]![EOData]![Org] Is Null
 
Thanks, this worked fine. One more question:

I have a second query (named 'Output_Subtotal') that
will "group by" the following fields:

Org, if only an org is selected from combo box
Org, then BU, if both Org and a BU is selected from combo
Org, then BU, then IOO, if an Org, BU, and IBO are selected

How do I set-up my Total Row for the BU, IOO, IBO fields
in the event that either a selection is made from one of
these combo boxes and if one is not selected. If I
use "group by" for all of these and nothing is selected
for combo box, I nothing is in the query results. If I
use "where" or "expression", then it won't ever group by
these fields if a selection is made from the respective
combo box.


-----Original Message-----
Steve said:
I have a form with five combo boxes, each have a row
source that pulls data from a table with the same name as
the combo box.

Org (10 to choose from) pulls from table named "Org"
BU (4 to choose from) pulls from talble named "BU"
IOO (6 to choose from) pulls from table named "IOO"
IBO (100 to choose from) pulls from table named "IBO"
Item (100,000 to choose from) pulls from table "ALL"

I have a command button that runs a query
named "Output_Detail" on click of the command button. The
query has the following in the criteria line for these
fields:

Org field: [Forms]![EOData]![Org]
BU field: [Forms]![EOData]![BU]
IOO field: [Forms]![EOData]![IOO]
IBO field: [Forms]![EOData]![IBO]
Item field: [Forms]![EOData]![Item]

The problem is this: if only the org is selected, the
query is interpreting the other fields as having some
value that is not found in my table (which is
named "ALL"), and the query returns no records. I added a
blank record to the BU, IOO, and IBO tables hoping that if
nothing was selected in these combo boxes, that the field
criteria in my query would default to blanks (as if there
were no filters for those fields), and would then give me
all records for the org selected (for example, if F01 was
selected for org, the only criteria/filter in my query
would be to pull all records from my "ALL" table that have
F01 as the org).
Click to expand...


You need to specify the criteria so that it returns True
when a combo box has not had a value selected. Change each
criteria to look like:

Org field:
[Forms]![EOData]![Org] Or [Forms]![EOData]![Org] Is Null
Click to expand...
 
steve said:
I have a second query (named 'Output_Subtotal') that
will "group by" the following fields:

Org, if only an org is selected from combo box
Org, then BU, if both Org and a BU is selected from combo
Org, then BU, then IOO, if an Org, BU, and IBO are selected

How do I set-up my Total Row for the BU, IOO, IBO fields
in the event that either a selection is made from one of
these combo boxes and if one is not selected. If I
use "group by" for all of these and nothing is selected
for combo box, I nothing is in the query results. If I
use "where" or "expression", then it won't ever group by
these fields if a selection is made from the respective
combo box.
Click to expand...


Sorry, but I don't follow that, maybe you should post the
query's SQL so I can what you're referring to.
 
Here you go....

SELECT [ALL].Inv_Org, Sum([ALL].[Prior_Reserved_(LC)]) AS
[SumOfPrior_Reserved_(LC)], Sum([ALL].[Ending_Inventory_
(LC)]) AS [SumOfEnding_Inventory_(LC)], Sum([ALL].
[New_Reserve_(LC)_Based_on_Demand____/Consumption]) AS
[SumOfNew_Reserve_(LC)_Based_on_Demand____/Consumption],
Sum([ALL].[Change_in_Reserve_(LC)]) AS
[SumOfChange_in_Reserve_(LC)], Sum([ALL].
[Net_change_in_RESERVED_(LC)]) AS
[SumOfNet_change_in_RESERVED_(LC)], Sum([ALL].
[Ending_RESERVED_(LC)]) AS [SumOfEnding_RESERVED_(LC)], Sum
([ALL].USD_Inv) AS SumOfUSD_Inv, Count([ALL].Item) AS
CountOfItem, [ALL].IOO, [ALL].IBO, [ALL].BU
FROM [ALL]
GROUP BY [ALL].Inv_Org, [ALL].IOO, [ALL].IBO, [ALL].BU
HAVING ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].IBO)=
[Forms]![EOData]![IBO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].IBO)=
[Forms]![EOData]![IBO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IBO)=[Forms]![EOData]![IBO]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].IBO)=[Forms]![EOData]![IBO])
AND (([ALL].BU)=[Forms]![EOData]![BU])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].IOO)=
[Forms]![EOData]![IOO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].BU)=[Forms]![EOData]![BU])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].IOO)=[Forms]![EOData]![IOO])
AND (([ALL].IBO)=[Forms]![EOData]![IBO])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].IOO)=
[Forms]![EOData]![IOO]) AND (([ALL].IBO)=[Forms]![EOData]!
[IBO])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IBO)=[Forms]![EOData]![IBO])) OR ((([ALL].Inv_Org)=
[Forms]![EOData]![Org]) AND (([ALL].IBO)=[Forms]![EOData]!
[IBO])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO])) OR ((([ALL].Inv_Org)=
[Forms]![EOData]![Org]) AND (([ALL].IOO)=[Forms]![EOData]!
[IOO]))
WITH OWNERACCESS OPTION;
 
Well, I'm still not sure I follow what you're trying to do
and I'm not enough of an SQL expert to be sure what this is
all about, but I think you should be grouping on an
expression that returns a constant value if the
corresponding combo box has a Null value (i.e. nothing
selected).

Here's the general idea of what I'm thinking might work:

SELECT Inv_Org,
Sum([Prior_Reserved_(LC)]) AS [SumOfPrior_Reserved_(LC)],
. . .,
Count(Item) AS CountOfItem,
IIf(Forms!EOData!IOO Is Null, "None", IOO),
IIf(Forms!EOData!IBO Is Null, "None", IBO),
IIf(Forms!EOData!BU Is Null, "None", BU),
FROM ALL
GROUP BY [ALL].Inv_Org,
IIf(Forms!EOData!IOO Is Null, "None", IOO),
IIf(Forms!EOData!IBO Is Null, "None", IBO),
IIf(Forms!EOData!BU Is Null, "None", BU),

You may or may not want to use a WHERE or HAVING clause
depending on if this generates results close to what you
want.

If I'm completely off base with this, then you should try to
clarify your requirements and post this question to the
queries newsgroup.
--
Marsh
MVP [MS Access]


Here you go....

SELECT [ALL].Inv_Org, Sum([ALL].[Prior_Reserved_(LC)]) AS
[SumOfPrior_Reserved_(LC)], Sum([ALL].[Ending_Inventory_
(LC)]) AS [SumOfEnding_Inventory_(LC)], Sum([ALL].
[New_Reserve_(LC)_Based_on_Demand____/Consumption]) AS
[SumOfNew_Reserve_(LC)_Based_on_Demand____/Consumption],
Sum([ALL].[Change_in_Reserve_(LC)]) AS
[SumOfChange_in_Reserve_(LC)], Sum([ALL].
[Net_change_in_RESERVED_(LC)]) AS
[SumOfNet_change_in_RESERVED_(LC)], Sum([ALL].
[Ending_RESERVED_(LC)]) AS [SumOfEnding_RESERVED_(LC)], Sum
([ALL].USD_Inv) AS SumOfUSD_Inv, Count([ALL].Item) AS
CountOfItem, [ALL].IOO, [ALL].IBO, [ALL].BU
FROM [ALL]
GROUP BY [ALL].Inv_Org, [ALL].IOO, [ALL].IBO, [ALL].BU
HAVING ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].IBO)=
[Forms]![EOData]![IBO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].IBO)=
[Forms]![EOData]![IBO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IBO)=[Forms]![EOData]![IBO]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].IBO)=[Forms]![EOData]![IBO])
AND (([ALL].BU)=[Forms]![EOData]![BU])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].IOO)=
[Forms]![EOData]![IOO]) AND (([ALL].BU)=[Forms]![EOData]!
[BU])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].BU)=[Forms]![EOData]![BU])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].BU)=
[Forms]![EOData]![BU])) OR ((([ALL].Inv_Org)=[Forms]!
[EOData]![Org]) AND (([ALL].IOO)=[Forms]![EOData]![IOO])
AND (([ALL].IBO)=[Forms]![EOData]![IBO])) OR
((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND (([ALL].IOO)=
[Forms]![EOData]![IOO]) AND (([ALL].IBO)=[Forms]![EOData]!
[IBO])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IBO)=[Forms]![EOData]![IBO])) OR ((([ALL].Inv_Org)=
[Forms]![EOData]![Org]) AND (([ALL].IBO)=[Forms]![EOData]!
[IBO])) OR ((([ALL].Inv_Org)=[Forms]![EOData]![Org]) AND
(([ALL].IOO)=[Forms]![EOData]![IOO])) OR ((([ALL].Inv_Org)=
[Forms]![EOData]![Org]) AND (([ALL].IOO)=[Forms]![EOData]!
[IOO]))
WITH OWNERACCESS OPTION;



-----Original Message-----
Click to expand...
Click to expand...
 
Back
Top