Parameterized query in form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimS - 09 Nov 2007 20:12 GMT

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?

Signature

Jim

Reply to this Message

KARL DEWEY - 09 Nov 2007 21:36 GMT

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

Signature

KARL DEWEY
Build a little - Test a little

> 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
[quoted text clipped - 13 lines]
>
> Is there a more sensible way to do this?

Reply to this Message

JimS - 09 Nov 2007 21:50 GMT

Karl, thank you...

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

Signature

Jim

> Try this ---
> WHERE MICAPID =
[quoted text clipped - 18 lines]
> >
> > Is there a more sensible way to do this?

Reply to this Message

KARL DEWEY - 09 Nov 2007 23:17 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> Karl, thank you...
>
[quoted text clipped - 23 lines]
> > >
> > > Is there a more sensible way to do this?

Reply to this Message

John Spencer - 10 Nov 2007 15:53 GMT

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
'====================================================

Reply to this Message