Filtering Using Combo box

[Bob Quintal]

Hi all

I have a form that shows ID, DESCRIPTION, COST, PERCENTAGE &
YEAR. The record source of this form is a query. I've also
placed an unbound combo box in the form and for the criteria
of the field YEAR I've made a referance to the value of the
combo box so that when the user selects an entry from this
combo box the form is filtered based on the value selected..

What I want to do is instead of having one year only being
displayed on the form I'd like to be a able to see any two
years together, e.g. the combo box above COST, PERCENTAGE &
YEAR will display 2007 and if the user wants to comapre with
previous year then I'd have a second combo box but this time
above the field (COST, PERCENTAGE & YEAR) i.e the same field
is placed in the form twice.

Any ideas how to do this because if i specify a criteria for
the second year field then it just places with the first
cirtera

SQL Statement used;
SELECT tbl_COSTAPPEND.COSTID, tbl_COSTAPPEND.COSTBinNumber,
tbl_COSTAPPEND.COSTStandardPerKg,
tbl_COSTAPPEND.COSTPercentageCopper, tbl_BIN.BINDescription,
tbl_FAMILY.FAMILYType, tbl_COSTAPPEND.COSTYear,
tbl_COSTAPPEND.COSTStandardPerKg,
tbl_COSTAPPEND.COSTPercentageCopper, tbl_COSTAPPEND.COSTYear
FROM tbl_FAMILY INNER JOIN (tbl_BIN INNER JOIN tbl_COSTAPPEND
ON tbl_BIN.BINID = tbl_COSTAPPEND.COSTBinNumber) ON
tbl_FAMILY.FAMILYID = tbl_BIN.BINFamilyType
WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter
050707]! [Text34]));

I'd like to avoid using a subform in possible

Thanks

WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter
050707]![Text34]) OR ((tbl_COSTAPPEND.COSTYear)=[Forms]!
[frm_Cost Filter 050707]![Text35]));

To do this from the query builder, just add the second combobox
reference underneath (next row down) the existing one.

 

[Asif]

Hi all

I have a form that shows ID, DESCRIPTION, COST, PERCENTAGE & YEAR. The
record source of this form is a query. I've also placed an unbound
combo box in the form and for the criteria of the field YEAR I've made
a referance to the value of the combo box so that when the user
selects an entry from this combo box the form is filtered based on the
value selected..

What I want to do is instead of having one year only being displayed
on the form I'd like to be a able to see any two years together, e.g.
the combo box above COST, PERCENTAGE & YEAR will display 2007 and if
the user wants to comapre with previous year then I'd have a second
combo box but this time above the field (COST, PERCENTAGE & YEAR) i.e
the same field is placed in the form twice.

Any ideas how to do this because if i specify a criteria for the
second year field then it just places with the first cirtera

SQL Statement used;
SELECT tbl_COSTAPPEND.COSTID, tbl_COSTAPPEND.COSTBinNumber,
tbl_COSTAPPEND.COSTStandardPerKg, tbl_COSTAPPEND.COSTPercentageCopper,
tbl_BIN.BINDescription, tbl_FAMILY.FAMILYType,
tbl_COSTAPPEND.COSTYear, tbl_COSTAPPEND.COSTStandardPerKg,
tbl_COSTAPPEND.COSTPercentageCopper, tbl_COSTAPPEND.COSTYear
FROM tbl_FAMILY INNER JOIN (tbl_BIN INNER JOIN tbl_COSTAPPEND ON
tbl_BIN.BINID = tbl_COSTAPPEND.COSTBinNumber) ON tbl_FAMILY.FAMILYID =
tbl_BIN.BINFamilyType
WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter 050707]!
[Text34]));

I'd like to avoid using a subform in possible

Thanks

 

[Brendan Reynolds]

record source of this form is a query. I've also placed an unbound
combo box in the form and for the criteria of the field YEAR I've made
a referance to the value of the combo box so that when the user
selects an entry from this combo box the form is filtered based on the
value selected..

What I want to do is instead of having one year only being displayed
on the form I'd like to be a able to see any two years together, e.g.
the combo box above COST, PERCENTAGE & YEAR will display 2007 and if
the user wants to comapre with previous year then I'd have a second
combo box but this time above the field (COST, PERCENTAGE & YEAR) i.e
the same field is placed in the form twice.

WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter 050707]!
[Text34]));

<snip>

WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter 050707]!
[Text34]) OR ((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter
050707]![YourOtherControlName]) );

 

[kingston via AccessMonster.com]

Add the second combobox (NewText34) to your form and modify your SQL
statement:

SELECT... WHERE (((tbl_COSTAPPEND.COSTYear=[Forms]![frm_Cost Filter 050707]!
[Text34]) OR (tbl_COSTAPPEND.COSTYear=[Forms]![frm_Cost Filter 050707]!
[NewText34]));

Figure out how you want to handle Nulls and possible input mistakes and this
should give you two years' data.

Hi all

I have a form that shows ID, DESCRIPTION, COST, PERCENTAGE & YEAR. The
record source of this form is a query. I've also placed an unbound
combo box in the form and for the criteria of the field YEAR I've made
a referance to the value of the combo box so that when the user
selects an entry from this combo box the form is filtered based on the
value selected..

What I want to do is instead of having one year only being displayed
on the form I'd like to be a able to see any two years together, e.g.
the combo box above COST, PERCENTAGE & YEAR will display 2007 and if
the user wants to comapre with previous year then I'd have a second
combo box but this time above the field (COST, PERCENTAGE & YEAR) i.e
the same field is placed in the form twice.

Any ideas how to do this because if i specify a criteria for the
second year field then it just places with the first cirtera

SQL Statement used;
SELECT tbl_COSTAPPEND.COSTID, tbl_COSTAPPEND.COSTBinNumber,
tbl_COSTAPPEND.COSTStandardPerKg, tbl_COSTAPPEND.COSTPercentageCopper,
tbl_BIN.BINDescription, tbl_FAMILY.FAMILYType,
tbl_COSTAPPEND.COSTYear, tbl_COSTAPPEND.COSTStandardPerKg,
tbl_COSTAPPEND.COSTPercentageCopper, tbl_COSTAPPEND.COSTYear
FROM tbl_FAMILY INNER JOIN (tbl_BIN INNER JOIN tbl_COSTAPPEND ON
tbl_BIN.BINID = tbl_COSTAPPEND.COSTBinNumber) ON tbl_FAMILY.FAMILYID =
tbl_BIN.BINFamilyType
WHERE (((tbl_COSTAPPEND.COSTYear)=[Forms]![frm_Cost Filter 050707]!
[Text34]));

I'd like to avoid using a subform in possible

Thanks

 

[Asif]

Thanks guys for your replies, I've modified the SQL code as per your
suggestion

However as I have two combo box's and the same field placed twice in
the form (for comparison purposes) If i change the value in Combobox1
then the filter is applied to all the fields but I only want it to be
applied to the first 3 sets of the field, and likewise if i change the
value of combo box 2 then it only filters the fields below it. I've
tried to give a rough layout of the form as it stands just to make
things clearer.

Combo box 1 Combo
box 2
COST % YEAR COST
% YEAR

Thanks once again

 

[kingston via AccessMonster.com]

Are the combo boxes bound to the same field (are they bound at all)? I don't
understand what you mean by "the first 3 sets of the field". Anyway, if you
want two sets of data from the same source side-by-side, an easy way to do
this is to use two forms (or one form with two instances, i.e. opened twice).
You can construct the data in a temporary table or crosstab query too, but
try using subforms first. Is there a reason why subforms won't work for you?

 

[Asif]

No combo boxes are unbound but they seem to refer to the same field.
If a change the value of Combobox 1 then the fields under combo box 2
are filtered as well

 

[Asif]

I take it no one has experianced this problem??