Parameterized query in form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form exports a query. The query needs to select some or all "MICAP"s and
some or all "Project"s. My form has an option box [optMICAPS] and another
option box [optProjects].

When the option radio button is cleared, a combo box is made visible for
selecting a MICAP or Project (cbMICAPID, etc.)

The underlying query has a "where" clause like:
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],"*",[Forms]![frmLaborHistoryExtract]![cbMICAPID])

Similarly for the Project selection.

The MICAPID is numeric, as is the ProjectID. When the above IIf evaluates to
"*", the query selects nothing.

Is there a more sensible way to do this?
 
Try this ---
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS], Is Not Null,
[Forms]![frmLaborHistoryExtract]![cbMICAPID])
 
Karl, thank you...

Syntax seems wrong... Isn't IIF() looking for a quoted string, variable, or
other object?
--
Jim


KARL DEWEY said:
Try this ---
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS], Is Not Null,
[Forms]![frmLaborHistoryExtract]![cbMICAPID])

--
KARL DEWEY
Build a little - Test a little


JimS said:
My form exports a query. The query needs to select some or all "MICAP"s and
some or all "Project"s. My form has an option box [optMICAPS] and another
option box [optProjects].

When the option radio button is cleared, a combo box is made visible for
selecting a MICAP or Project (cbMICAPID, etc.)

The underlying query has a "where" clause like:
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],"*",[Forms]![frmLaborHistoryExtract]![cbMICAPID])

Similarly for the Project selection.

The MICAPID is numeric, as is the ProjectID. When the above IIf evaluates to
"*", the query selects nothing.

Is there a more sensible way to do this?
Click to expand...
Click to expand...
 
IIF is evaluating the statement and returning results based on True or False.
It can check for Is Null or Is Not Null, >, <, =, >=, <=, <>, Between X And
Y, and a whole bunch of comparrisions.

--
KARL DEWEY
Build a little - Test a little


JimS said:
Karl, thank you...

Syntax seems wrong... Isn't IIF() looking for a quoted string, variable, or
other object?
--
Jim


KARL DEWEY said:
Try this ---
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS], Is Not Null,
[Forms]![frmLaborHistoryExtract]![cbMICAPID])

--
KARL DEWEY
Build a little - Test a little


JimS said:
My form exports a query. The query needs to select some or all "MICAP"s and
some or all "Project"s. My form has an option box [optMICAPS] and another
option box [optProjects].

When the option radio button is cleared, a combo box is made visible for
selecting a MICAP or Project (cbMICAPID, etc.)

The underlying query has a "where" clause like:
WHERE MICAPID =
IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],"*",[Forms]![frmLaborHistoryExtract]![cbMICAPID])

Similarly for the Project selection.

The MICAPID is numeric, as is the ProjectID. When the above IIf evaluates to
"*", the query selects nothing.

Is there a more sensible way to do this?
Click to expand...
Click to expand...
Click to expand...
 
Try the following which will probably be slow. It does rely on there
being a value in MicapsID and ProjectID to return a record.

WHERE IIf([Forms]![frmLaborHistoryExtract]![optMICAPS],
[MicapID] is not null,
[MicapID=[Forms]![frmLaborHistoryExtract]![cbMICAPID])

AND

IIf([Forms]![frmLaborHistoryExtract]![optProject],
[ProjectID] is not null,
[ProjectID=[Forms]![frmLaborHistoryExtract]![cbProjectID])

If that is too slow, you might have to consider building the query on
the fly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top