Invalid use if 'Null' on date field. Date field intentionally left blank

[StarfishJoe]

I have two text boxes on a form formatted with a Short-Date mask
One of these two date fields may be left blank, or they both may be blank.

They both compare a date range and return all records within that date
range.
If both Date Fields on form are left blank. All records are returned.
If both are not left blank, then the results are all dates within that
range.
(so far so good)
If first date field is entered, and the second date field left blank, All
records equal to or later that first date are returned. (So far it seems to
work - or it did)

Problem:
If first Date Field left BLANK and second date field contains a valid date,
then the result should be All records on or before that date. I am getting
"Invalid Use of Null"
I started out trying to test for null and convert null values to empty
strings: ""
If isnull(txtDateFrom)=true then txtDateFrom = ""
Then I tried testing for a valid date:
if isdate(txtDateFrom=false) then....
It seems both of these are returning "invalid use of 'null'

Are these two equivilant; 'null' and " " ?
How should I represent an empty date textbox and compare it with another
date Text Box?

If txtDateFrom = ( blank, null, or empty string) and txtDateTo =< (Some
valid date like 06/19/05) then....

StarfishJoe

 

[Allan Murphy]

Remove the =true and use just If isnull(txtDateFrom).

Try this code,
If isnull(txtdatefrom) then
' date from has no value now check for date to
ifisnull(txtdateto) then
' all dates are blank
print all records
else
' date to is not blank
print records to this date
end if
else
' date from is not blank now check for date to
ifisnull(txtdateto) then
' dates to is blank
print all records from date
else
' both dates are entered
print records between this
end if
end if

 

[Allen Browne]

Null is not the same as a zero-length string (ZLS), i.e. "".

A ZLS is not a valid piece of data to store in a date/time type field, so
attempting to assign it will fail.

For an example of how to build a WhereCondition to open a report based on
start and end dates that could be blanks, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

For more info on problems with Nulls, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html