Re: IF THEN statements in SQL

Hi,


Ha! now I see, sorry!

"YourFunction" is the control, on the Form, supplying the sign of the
comparison, basically a combo box with two possible values, "=" and ">".


Hoping it may help,
Vanderghast, Access MVP



"jeff" <jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D6A443DF-0E5E-406B-889B-370194356BC8@xxxxxxxxxxxxxxxx
thanks for the input... I'm working on using your solution right now, but
I
don't know what "YourFunction" is. Where does that come from?

-jeff


"Michel Walsh" wrote:

Hi,




For 1), add OR IS NULL to get

LIKE iif( ... ) OR IS NULL


in the grid.

For 2), that is a preferable to do it in the SQL view, not in the grid.
The
WHERE clause could be something like:


WHERE (FORMS!FormName!YourDropDown = "ANY")
OR iif(FORMS!FormName!YourFunction = ">",
YourFieldName > FORMS!FormName!YourDropDown ,
YourFieldName = FORMS!FormName!YourDropDown )
OR YourFieldName IS NULL




NOTE that it MAY be preferable to write the SQL statement at runtime, in
this case, say, to get just:


.... WHERE YourFieldName > FORMS!FormName!YourDropDown


since then, the optimizer will be able to use the index, if any, and the
runtime solution could be faster. A clause that is too generic is often
not
as optimized as a clause that is "specific".


Hoping it may help,
Vanderghast, Access MVP


"jeff" <jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E31FEC97-3B62-41D0-AC91-486E999AE13F@xxxxxxxxxxxxxxxx
Thanks! That worked great, but of course, now it has formed more
questions...

1. It gets rid of null values in the table. How can I keep those in
the
query results?

2. In the ELSE portion, can I use a > to get all results greater than
the
dropdown selection?

-jeff


"KARL DEWEY" wrote:

Open the select query in design view. Put this in the criteria row --
Like IIF([Forms]![YourFormName]![YourDropDown] = "Any",
"*",[Forms]![YourFormName]![YourDropDown])

"jeff" wrote:

I want to query data using a dropdown box on a form. The possible
selections
are:

Any, 5,10,20,30

So, there are basically 2 possible selections- "Any" or A Number.
If
it's
"Any", then I want the query to let all the data through. If it's A
Number,
then it should select records that match.

How do I do this in SQL? I understand the IF,THEN format, but I
don't
know
how to integrate it with SELECT, FROM, and WHERE. Please help!

-jeff





.

Relevant Pages

  • Re: IF THEN statements in SQL
    ... try to use single quotes rather than double quotes. ... WHERE clause could be something like: ... YourFieldName> FORMS!FormName!YourDropDown, ... NOTE that it MAY be preferable to write the SQL statement at runtime, ...
    (microsoft.public.access.queries)
  • Re: IF THEN statements in SQL
    ... "jeff" wrote: ... For 2), that is a preferable to do it in the SQL view, not in the grid. ... WHERE clause could be something like: ... YourFieldName> FORMS!FormName!YourDropDown, ...
    (microsoft.public.access.queries)
  • Re: IF THEN statements in SQL
    ... For 2), that is a preferable to do it in the SQL view, not in the grid. ... YourFieldName> FORMS!FormName!YourDropDown, ... A clause that is too generic is often not ...
    (microsoft.public.access.queries)
  • Re: IF THEN statements in SQL
    ... For 2), that is a preferable to do it in the SQL view, not in the grid. ... YourFieldName> FORMS!FormName!YourDropDown, ... A clause that is too generic is often not ...
    (microsoft.public.access.queries)
  • Re: IF THEN statements in SQL
    ... don't know what "YourFunction" is. ... For 2), that is a preferable to do it in the SQL view, not in the grid. ... WHERE clause could be something like: ... YourFieldName> FORMS!FormName!YourDropDown, ...
    (microsoft.public.access.queries)