IIf Statements and Parameter Queries

M

maralkarimi1

I want my query to ask the user for either: All, <500, or >500 pric
range. I do this through a parameter. If they say all, I want the quer
to show all the items, if it's <500, the items that are less tahn $500
and so on, I have the following statement in my [UnitPrice] column'
critiria row:

IIf([Enter Price Range]="<500", [UnitPrice]<500,(IIf([Enter Pric
Range]=">500",[UnitPrice]>500,[UnitPrice])))

but the query does not execute this statement. What should I do
 
S

S.Clark [Access MVP]

How about:

WHERE UnitPrice Between [Enter Low Value] And [Enter High Value]

For all, they could enter 0 to 99999999

Otherwise, build a form with a combobox that does the work.

Steve Clark, Access MVP
fmsinc.com/consulting
 
J

John Vinson

I want my query to ask the user for either: All, <500, or >500 price
range. I do this through a parameter. If they say all, I want the query
to show all the items, if it's <500, the items that are less tahn $500,
and so on, I have the following statement in my [UnitPrice] column's
critiria row:

IIf([Enter Price Range]="<500", [UnitPrice]<500,(IIf([Enter Price
Range]=">500",[UnitPrice]>500,[UnitPrice])))

but the query does not execute this statement. What should I do?
Click to expand...

The problem is that a parameter can be used to enter a *value* to be
searched, but it cannot enter an operator such as > or <.

Try this instead, as a WHERE criterion in your query (open it in SQL
view and edit the WHERE clause:

WHERE ([Enter price range] = "All")
OR
([Enter price range] = "<500" AND [UnitPrice] < 500)
OR
([Enter price range] = ">500" AND [UnitPrice] > 500)

Note that if the user types "> 500" (with a blank) or "<400" (with a
different number) they won't get anything at all. You may want to use
a Form with a combo box specifying the allowed choices.

John W. Vinson[MVP]
 
Top