Query - parameter passed from pop-up form

T

Traci

Right now I've got a query running off of a button on a form that accepts the
value selected. This drop down has three values that are similar:

* Data
* Voice
* Data and Voice

If the user selects Data and Voice, I would like the query to return records
that have Data, Voice, and 'Data and Voice'. However, if they choose Data,
it should only pull records with data. If they choose Voice, it should only
return records with voice. There are also other discrete values the user can
select, that should return records with these values only.

Is this possible? Should I use a LIKE operator?
 
K

KARL DEWEY

Use an IIF statement for criteria.
IIF([Forms]![YourForm]![YourDropDown]
="Data","Data",IIF([Forms]![YourForm]![YourDropDown]
="Voice","Voice",IIF([Forms]![YourForm]![YourDropDown] ="Data and Voice",
"Data" OR "Voice" OR "Data and Voice",)))
 
T

Traci

Thanks Karl. I implemented this and it seems to work okay for Data and Voice
separately, however, when I select "Data and Voice" I get the following error
message. I've checked and double checked what I paste with what you have here
to be sure I didn't drop a part of it.

This expression is typed incorrectly or is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.

Here is what my statement looks like:

SELECT RLL_Main.Project_Type, RLL_Main.Project_Name, RLL_Main.Description,
RLL_Main.Resolution, RLL_Main.Resp_Impl_Action, RLL_Main.Root_Cause,
RLL_Main.Keywords, RLL_Main.Category, RLL_Main.Probability, RLL_Main.Impact,
RLL_Main.Severity, RLL_Main.Owner
FROM RLL_Main
WHERE
(((RLL_Main.Project_Type)=IIf([Forms]![SelectProjectType]![ProjectType]="Data","Data",IIf([Forms]![SelectProjectType]![ProjectType]="Voice","Voice",IIf([Forms]![SelectProjectType]![ProjectType]="Data
and Voice",(RLL_Main.Project_Type)="Data" Or (RLL_Main.Project_Type)="Voice"
Or (RLL_Main.Project_Type)="Data and Voice")))));



KARL DEWEY said:
Use an IIF statement for criteria.
IIF([Forms]![YourForm]![YourDropDown]
="Data","Data",IIF([Forms]![YourForm]![YourDropDown]
="Voice","Voice",IIF([Forms]![YourForm]![YourDropDown] ="Data and Voice",
"Data" OR "Voice" OR "Data and Voice",)))

Traci said:
Right now I've got a query running off of a button on a form that accepts the
value selected. This drop down has three values that are similar:

* Data
* Voice
* Data and Voice

If the user selects Data and Voice, I would like the query to return records
that have Data, Voice, and 'Data and Voice'. However, if they choose Data,
it should only pull records with data. If they choose Voice, it should only
return records with voice. There are also other discrete values the user can
select, that should return records with these values only.

Is this possible? Should I use a LIKE operator?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
WHERE
(((RLL_Main.Project_Type)=IIf([Forms]![SelectProjectType]![ProjectType]="Data","Data",IIf([Forms]![SelectProjectType]![ProjectType]="Voice","Voice",IIf([Forms]![SelectProjectType]![ProjectType]="Data
and Voice","Data" Or "Voice" Or "Data and Voice")))));


Traci said:
Thanks Karl. I implemented this and it seems to work okay for Data and Voice
separately, however, when I select "Data and Voice" I get the following error
message. I've checked and double checked what I paste with what you have here
to be sure I didn't drop a part of it.

This expression is typed incorrectly or is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.

Here is what my statement looks like:

SELECT RLL_Main.Project_Type, RLL_Main.Project_Name, RLL_Main.Description,
RLL_Main.Resolution, RLL_Main.Resp_Impl_Action, RLL_Main.Root_Cause,
RLL_Main.Keywords, RLL_Main.Category, RLL_Main.Probability, RLL_Main.Impact,
RLL_Main.Severity, RLL_Main.Owner
FROM RLL_Main
WHERE
(((RLL_Main.Project_Type)=IIf([Forms]![SelectProjectType]![ProjectType]="Data","Data",IIf([Forms]![SelectProjectType]![ProjectType]="Voice","Voice",IIf([Forms]![SelectProjectType]![ProjectType]="Data
and Voice",(RLL_Main.Project_Type)="Data" Or (RLL_Main.Project_Type)="Voice"
Or (RLL_Main.Project_Type)="Data and Voice")))));



KARL DEWEY said:
Use an IIF statement for criteria.
IIF([Forms]![YourForm]![YourDropDown]
="Data","Data",IIF([Forms]![YourForm]![YourDropDown]
="Voice","Voice",IIF([Forms]![YourForm]![YourDropDown] ="Data and Voice",
"Data" OR "Voice" OR "Data and Voice",)))

Traci said:
Right now I've got a query running off of a button on a form that accepts the
value selected. This drop down has three values that are similar:

* Data
* Voice
* Data and Voice

If the user selects Data and Voice, I would like the query to return records
that have Data, Voice, and 'Data and Voice'. However, if they choose Data,
it should only pull records with data. If they choose Voice, it should only
return records with voice. There are also other discrete values the user can
select, that should return records with these values only.

Is this possible? Should I use a LIKE operator?
Click to expand...
Click to expand...
Click to expand...
 
Top