Multiple criteria taken from 2 seperate forms

  • Thread starter Thread starter sashabaz
  • Start date Start date
S

sashabaz

Hi all,

I am trying to set 2 different criteria on the same field in a query. The
query is based around a project name.

I want it to take a value from "frmProjectMilestones" or "frmProjectSelect"
(whichever form is being used first). In each case, the combo box has the
same name. Here is the expression I have used in the Criteria:

[Forms]![frmProjectMilestones]![cboProjectSelect] Or
[Forms]![frmProjectSelect]![cboProjectSelect]

Strangely, if I only put 1 of the expressions in the query it seems to work
fine. If both are there, it allows the one that has been entered and then
asks for the other to also be entered.

How can I get it to be an either/or criteria??
 
Use an IIF statement --
Like IIF([Forms]![frmProjectMilestones]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectMilestones]![cboProjectSelect],
IIF([Forms]![frmProjectSelect]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectSelect]![cboProjectSelect], "*"))

If nothing selected in first form it checks the second. If second has no
selection then it pulls all.
 
Hi Karl,

I used that IIf statment, it sort of works, but when the first part is blank
(i.e. [frmProjectMilestones]![cboProjectSelect]) then the prompt box still
appears asking for frmProjectMilestones!cboProjectSelect to be entered. Is
there a way to stop this from appearing??

KARL DEWEY said:
Use an IIF statement --
Like IIF([Forms]![frmProjectMilestones]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectMilestones]![cboProjectSelect],
IIF([Forms]![frmProjectSelect]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectSelect]![cboProjectSelect], "*"))

If nothing selected in first form it checks the second. If second has no
selection then it pulls all.
--
KARL DEWEY
Build a little - Test a little


sashabaz said:
Hi all,

I am trying to set 2 different criteria on the same field in a query. The
query is based around a project name.

I want it to take a value from "frmProjectMilestones" or "frmProjectSelect"
(whichever form is being used first). In each case, the combo box has the
same name. Here is the expression I have used in the Criteria:

[Forms]![frmProjectMilestones]![cboProjectSelect] Or
[Forms]![frmProjectSelect]![cboProjectSelect]

Strangely, if I only put 1 of the expressions in the query it seems to work
fine. If both are there, it allows the one that has been entered and then
asks for the other to also be entered.

How can I get it to be an either/or criteria??
Click to expand...
Click to expand...
 
None that I know of -- others may have some ideas.
--
KARL DEWEY
Build a little - Test a little


sashabaz said:
Hi Karl,

I used that IIf statment, it sort of works, but when the first part is blank
(i.e. [frmProjectMilestones]![cboProjectSelect]) then the prompt box still
appears asking for frmProjectMilestones!cboProjectSelect to be entered. Is
there a way to stop this from appearing??

KARL DEWEY said:
Use an IIF statement --
Like IIF([Forms]![frmProjectMilestones]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectMilestones]![cboProjectSelect],
IIF([Forms]![frmProjectSelect]![cboProjectSelect] Is Not Null,
[Forms]![frmProjectSelect]![cboProjectSelect], "*"))

If nothing selected in first form it checks the second. If second has no
selection then it pulls all.
--
KARL DEWEY
Build a little - Test a little


sashabaz said:
Hi all,

I am trying to set 2 different criteria on the same field in a query. The
query is based around a project name.

I want it to take a value from "frmProjectMilestones" or "frmProjectSelect"
(whichever form is being used first). In each case, the combo box has the
same name. Here is the expression I have used in the Criteria:

[Forms]![frmProjectMilestones]![cboProjectSelect] Or
[Forms]![frmProjectSelect]![cboProjectSelect]

Strangely, if I only put 1 of the expressions in the query it seems to work
fine. If both are there, it allows the one that has been entered and then
asks for the other to also be entered.

How can I get it to be an either/or criteria??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top