If criteria is Null then show all.

[valntyn via AccessMonster.com]

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!

 

[Brendan Reynolds]

This is untested air-code, so the syntax may not be correct, it is intended
only to demonstrate the concept ...

Dim FirstDate As Date
Dim LastDate As Date

FirstDate = #1 Jan 100#
LastDate = #31 Dec 9999#

If Len(FirstDateTextBox.Text) > 0 Then
FirstDate = CDate(FirstDateTextBox.Text)
End If

If Len(LastDateTextBox.Text) > 0 Then
LastDate = CDate(LastDateTextBox.Text)
End If

If the user enters nothing in the text boxes, the variables will be
initialized with minimum and maximum values designed to include any values
that may be found within the database.