By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Query criteria based on 2 different form controls

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.