Multiple criteria taken from 2 seperate forms

[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??

 

[KARL DEWEY]

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.

 

[sashabaz]

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??

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

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??

 

[KARL DEWEY]

None that I know of -- others may have some ideas.
--
KARL DEWEY
Build a little - Test a little

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??

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

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??