IF THEN statements in SQL

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

Guest

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
 
Open the select query in design view. Put this in the criteria row --
Like IIF([Forms]![YourFormName]![YourDropDown] = "Any",
"*",[Forms]![YourFormName]![YourDropDown])
 
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 said:
Open the select query in design view. Put this in the criteria row --
Like IIF([Forms]![YourFormName]![YourDropDown] = "Any",
"*",[Forms]![YourFormName]![YourDropDown])

jeff said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
Open the select query in design view. Put this in the criteria row --
Like IIF([Forms]![YourFormName]![YourDropDown] = "Any",
"*",[Forms]![YourFormName]![YourDropDown])

jeff said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Open the select query in design view. Put this in the criteria row --
Like IIF([Forms]![YourFormName]![YourDropDown] = "Any",
"*",[Forms]![YourFormName]![YourDropDown])

:

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
Click to expand...
Click to expand...
Click to expand...
 
Hi,


YourFunction? or YourFieldName? in the last case, you change it for the
field having the values to be tested... you didn't supply it in your
original post (as far as I can tell); it is the field name under which you
were trying to build this criteria, in the grid. *If* it was a computed
expression, *not* a field name, *then* you have to repeat the expression
itself (not the alias of the expression, like Expr001, or similar, but the
expression itself).


Hoping it may help,
Vanderghast, Access MVP

jeff said:
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 said:
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 said:
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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
hello,

Seems to be getting close now. The only problem is that when I select "Any"
from the dropdown, the query gives an error message saying "something is
typed incorrectly or it is too complex." If the user selects "Any" then it
should return all records, otherwise it should return all values < "the
number selected". I just can't quite get my head around this! Here is a
copy of the code so far:

SELECT [Fwd P/E]
FROM [Screening Table]
WHERE (FORMS!Master!ESFwdPE="Any")
Or IIf(FORMS!Master!signCombo="<",
[Fwd P/E]<FORMS!Master!ESFwdPE,
[Fwd P/E]=FORMS!Master!ESFwdPE)
Or [Fwd P/E] Is Null;

Can't thank you enough for the help thus far...

-jeff



Michel Walsh said:
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 said:
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 said:
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


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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Got it to work! thanks so much for the help. I'm sure I'll be back soon...

-jeff


jeff said:
hello,

Seems to be getting close now. The only problem is that when I select "Any"
from the dropdown, the query gives an error message saying "something is
typed incorrectly or it is too complex." If the user selects "Any" then it
should return all records, otherwise it should return all values < "the
number selected". I just can't quite get my head around this! Here is a
copy of the code so far:

SELECT [Fwd P/E]
FROM [Screening Table]
WHERE (FORMS!Master!ESFwdPE="Any")
Or IIf(FORMS!Master!signCombo="<",
[Fwd P/E]<FORMS!Master!ESFwdPE,
[Fwd P/E]=FORMS!Master!ESFwdPE)
Or [Fwd P/E] Is Null;

Can't thank you enough for the help thus far...

-jeff



Michel Walsh said:
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 said:
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


:

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


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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Hi,


try to use single quotes rather than double quotes. The following worked
fine for me (List5 being a list box, f1 being my field name to be tested,
text0 supplying Any, or a value, and text2 supplying either "=" either ">"
:


Me.List5.RowSource = "SELECT f1 FROM table35 WHERE
(Forms!form49!text0='Any') OR iif(FORMS!form49!text2='>',
f1>FORMS!form49!text0, f1=FORMS!form49!text0) OR f1 IS NULL"



(that is just one long line of VBA code, the newsreader can break it on
multiple lines).



Hoping it may help,
Vanderghast, Access MVP


jeff said:
hello,

Seems to be getting close now. The only problem is that when I select
"Any"
from the dropdown, the query gives an error message saying "something is
typed incorrectly or it is too complex." If the user selects "Any" then
it
should return all records, otherwise it should return all values < "the
number selected". I just can't quite get my head around this! Here is a
copy of the code so far:

SELECT [Fwd P/E]
FROM [Screening Table]
WHERE (FORMS!Master!ESFwdPE="Any")
Or IIf(FORMS!Master!signCombo="<",
[Fwd P/E]<FORMS!Master!ESFwdPE,
[Fwd P/E]=FORMS!Master!ESFwdPE)
Or [Fwd P/E] Is Null;

Can't thank you enough for the help thus far...

-jeff



Michel Walsh said:
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 said:
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


:

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


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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top