Date Range Search Form ADP

[T.J. Bernard]

I have gotten some great help off of this message board,
so I thought I would throw out this delima and see if
anyone has any ideas on how to tackle the problem.

I have a form that allows the users to enter a range of
dates to search against the database. There are 4 date
fields it allows the users to search against, but they are
not required entry fields. If they enter in the first
range box for a date field, it finds all the records where
the date is >= that date. If they enter in the second
range box for a date field, it finds all the records where
the date is <= that date. If they enter in both range
boxes it finds all the records where the date is >= the
1st box and <= the 2nd box.

I am trying to think of how to do this using stored
procedures but I am running into some problems.

If I pass no values for the non-entered fields, it then
finds no records where the dates are >= Null and <= Null,
so that doesn't work. If I enter in ficticious dates for
the non-entered fields (1st box >= 1/1/1900 and 2nd box
<12/31/2003) then I don't ge the records where these non-
entered fields are null.

If anyone has any ideas on how to tackle this or where to
look for help I will greatly appreciate it.

Thank you,

T.J.

 

[Vadim Rapp]

TB> If I pass no values for the non-entered fields, it then
TB> finds no records where the dates are >= Null and <= Null,
TB> so that doesn't work. If I enter in ficticious dates for
TB> the non-entered fields (1st box >= 1/1/1900 and 2nd box
TB> <12/31/2003) then I don't ge the records where these non-
TB> entered fields are null.

between ISNULL(whatsinthe1stbox,'1/1/1900') and
ISNULL(whatsinthe2ndbox,'1/1/2900')

Vadim