Query criteria based on 2 different form controls

[neptune]

I have a query where each customer has an [ID1] or [ID2]. Sometimes
both fields for a customer are populated, but if [ID1] is null, then
[ID2] will be populated and vice versa. I have a form,
[frmEligibility], where I select a value for [cboID1] from a combo
box. In my query I set the criteria for [ID1] to
[Forms].[frmEligibility].[cboID1]. My query finds the proper values
for [ID1].

Now I also want to find the values if I select a value for [cboID2] in
a separate combo box. In both controls, OnChange, I set the value of
the opposite control = "" so only one control has a value at one time.
In my query I want to find the record based on the control that is
populated. If [cboID1] = "", I don't want any criteria for [ID1] in
the query and [ID2] should be equal to [cboID2].

I set the criteria for the [ID1] field to:
IIf([Forms].[frmEligibility].[cboID1]<>"",[Forms].[
frmEligibility].[cboID1])
I set the criteria for the [ID2] field to:
IIf([Forms].[frmEligibility].[cboID2]<>"",[Forms].[
frmEligibility].[cboID2])

This does not work and I want [cboID1] and [cboID2] to remain on the
same form. Any suggestions?
Thanks

[Bas Cost Budde]

neptune wrote:

I have a query where each customer has an [ID1] or [ID2]. Sometimes
both fields for a customer are populated, but if [ID1] is null, then
[ID2] will be populated and vice versa. I have a form,
[frmEligibility], where I select a value for [cboID1] from a combo
box. In my query I set the criteria for [ID1] to
[Forms].[frmEligibility].[cboID1]. My query finds the proper values
for [ID1].

Now I also want to find the values if I select a value for [cboID2] in
a separate combo box. In both controls, OnChange, I set the value of
the opposite control = "" so only one control has a value at one time.
In my query I want to find the record based on the control that is
populated. If [cboID1] = "", I don't want any criteria for [ID1] in
the query and [ID2] should be equal to [cboID2].

I set the criteria for the [ID1] field to:
IIf([Forms].[frmEligibility].[cboID1]<>"",[Forms].[
frmEligibility].[cboID1])
I set the criteria for the [ID2] field to:
IIf([Forms].[frmEligibility].[cboID2]<>"",[Forms].[
frmEligibility].[cboID2])

This does not work and I want [cboID1] and [cboID2] to remain on the
same form. Any suggestions?
Thanks

Maybe inverse the fields and the controls. If you put the form fields in
the top row (as if they were in the table) and use this as criteria:

Is Null or =[id1]

does that work?

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Pieter Linden]

bs**********@hotmail.com (neptune) wrote in message news:<c5**************************@posting.google. com>...

I have a query where each customer has an [ID1] or [ID2]. Sometimes
both fields for a customer are populated, but if [ID1] is null, then
[ID2] will be populated and vice versa. I have a form,
[frmEligibility], where I select a value for [cboID1] from a combo
box. In my query I set the criteria for [ID1] to
[Forms].[frmEligibility].[cboID1]. My query finds the proper values
for [ID1].

Now I also want to find the values if I select a value for [cboID2] in
a separate combo box. In both controls, OnChange, I set the value of
the opposite control = "" so only one control has a value at one time.
In my query I want to find the record based on the control that is
populated. If [cboID1] = "", I don't want any criteria for [ID1] in
the query and [ID2] should be equal to [cboID2].

I set the criteria for the [ID1] field to:
IIf([Forms].[frmEligibility].[cboID1]<>"",[Forms].[
frmEligibility].[cboID1])
I set the criteria for the [ID2] field to:
IIf([Forms].[frmEligibility].[cboID2]<>"",[Forms].[
frmEligibility].[cboID2])

This does not work and I want [cboID1] and [cboID2] to remain on the
same form. Any suggestions?
Thanks

Sounds like a normalization issue! One way of dealing with this is to
use a stored query and modify the SQL based on what's been chosen.
You'd just store the query with no WHERE clause, then trim off the
semi-colon, add the valid where clause based on what's been chosen and
update the SQL property of the querydef in question. then you can
open the query. Of course, if you normalized, you'd only have one
field to query, and your problems would go away.