By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

If criteria is Null then show all.

P: n/a
I have an .asp page that passes four variables to another .asp page by using
an HTML form. A SQL query then runs against an Access database using those
four variables. The variables are: "cnty", "rte", "bgn", and "nd".

My current SQL statement is as follows:

SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND Route='" &
rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn & ""

This works just fine, but I would like to add the ability to make "nd" and
"bgn" Null (leave those fields blank on the HTML form), and then return all
records that match the "cnty" and "rte" variables. I have an SQL Query in
the Access database that does this perfectly but I'm having trouble getting
the same functionality on the web.

Any help is greatly appreciated!

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....neral/200611/1

Mar 7 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Lew
valntyn via DBMonster.com wrote:
I have an .asp page that passes four variables to another .asp page by using
an HTML form. A SQL query then runs against an Access database using those
four variables. The variables are: "cnty", "rte", "bgn", and "nd".

My current SQL statement is as follows:

SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND Route='" &
rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn & ""

This works just fine, but I would like to add the ability to make "nd" and
"bgn" Null (leave those fields blank on the HTML form), and then return all
records that match the "cnty" and "rte" variables. I have an SQL Query in
the Access database that does this perfectly but I'm having trouble getting
the same functionality on the web.
Pseudocode:

if ( empty( cnty ))
SQL = "SELECT * FROM Construction WHERE Route='" & rte & "' AND Begin<=" &
nd & " AND Ending>=" & bgn

else
SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND Route='"
& rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn

-- Lew
Mar 9 '07 #2

P: n/a
Lew
Lew wrote:
valntyn via DBMonster.com wrote:
>I have an .asp page that passes four variables to another .asp page by
using
an HTML form. A SQL query then runs against an Access database using
those
four variables. The variables are: "cnty", "rte", "bgn", and "nd".

My current SQL statement is as follows:

SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND
Route='" &
rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn & ""
This works just fine, but I would like to add the ability to make "nd"
and
"bgn" Null (leave those fields blank on the HTML form), and then
return all
records that match the "cnty" and "rte" variables. I have an SQL
Query in
the Access database that does this perfectly but I'm having trouble
getting
the same functionality on the web.
Sorry, I focused on the wrong variable:

Pseudocode:

if ( empty( bgn ) OR empty( nd ) )
SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND
Route='" & rte & "' "

else
SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND
Route='" & rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn

-- Lew
Mar 9 '07 #3

P: n/a
"valntyn via DBMonster.com" <u29706@uweschreef in bericht
news:6ed7203954842@uwe...
My current SQL statement is as follows:

SQL = "SELECT * FROM Construction WHERE County='" & cnty & "' AND Route='"
&
rte & "' AND Begin<=" & nd & " AND Ending>=" & bgn & ""

This works just fine, but I would like to add the ability to make "nd" and
"bgn" Null (leave those fields blank on the HTML form), and then return
all
records that match the "cnty" and "rte" variables. I have an SQL Query in
the Access database that does this perfectly but I'm having trouble
getting
the same functionality on the web.
How about ...
FLAG = empty( bgn ) OR empty( nd ) ? 'true' : 'false';
SQL = "SELECT * FROM Construction WHERE County='" & cnty
& "' AND Route='" & rte
& "' AND ( " & FLAG
& " or Begin<=0" & nd & " AND Ending>=0" & bgn & " )"

Note: assuming numerical input for nd and bgn, thus prepending 0.

Leaving out vowels in variable names ... Hebrew background?

HansH
Mar 9 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.