select and different search results

[kari]

I am trying to search a database and bring back different results
depends on search criteria.
I have my sql statement setup to bring back all records between two dates,
but I also need to bring back all results if no dates were selected, and
presents "no records" if no records were found between the dates selected.

here is my sql statement to return results between two dates:
strSQL = "SELECT rep_id, il_name, type_name, fname, lname, phone, comment,
state_name " &_
"FROM inct_rpt, state, il, inct_type " &_
"WHERE state.state_id=inct_rpt.state_id " &_
"AND il.il_id=inct_rpt.il_id " &_
"AND inct_type.inct_type_id=inct_rpt.inct_type_id " &_
"and inct_rpt.update_date <= '" & request.querystring("before") & "'"
&_
"and inct_rpt.update_date > '" & request.querystring("after") & "' " &_
"ORDER BY inct_rpt.update_date "

[Robert Gelb]

You should create the SQL dynamically. If the user enters dates, then add
the date criteria to the SQL statement. If not, then execute without the
dates criteria and you should get all the data back.

--
Robert Gelb
www.vbRad.com
Source, Tips, Tricks, Components

"kari" <kari.l.macko@xo.com> wrote in message
news:3af9811c$1@news.devx.com...
>
> I am trying to search a database and bring back different results
> depends on search criteria.
> I have my sql statement setup to bring back all records between two dates,
> but I also need to bring back all results if no dates were selected, and
> presents "no records" if no records were found between the dates selected.
>
> here is my sql statement to return results between two dates:
> strSQL = "SELECT rep_id, il_name, type_name, fname, lname, phone, comment,
> state_name " &_
> "FROM inct_rpt, state, il, inct_type " &_
> "WHERE state.state_id=inct_rpt.state_id " &_
> "AND il.il_id=inct_rpt.il_id " &_
> "AND inct_type.inct_type_id=inct_rpt.inct_type_id " &_
> "and inct_rpt.update_date <= '" & request.querystring("before") & "'"
> &_
> "and inct_rpt.update_date > '" & request.querystring("after") & "' " &_
> "ORDER BY inct_rpt.update_date "
>