Sometimes an end date can have a null value which signifies an unspecified date in the future, similar to infinity. This presents a problem when comparing with non-null dates as it would treat the end date as being less than the target date instead of being greater. This is because null is always less than not null.
One way around this is to include the test for a null value in the comparison, as follows:-
read u_where (end_date >= target_date | end_date = "")
This tends to get rather cumbersome as this combined test has to be included everywhere. Leave it out just once and see the confusing results!
What we need is a way of testing the date with a simple comparison, like so:
read u_where (end_date >= target_date)
This can be done by holding unspecified end-dates on the database as the highest date available, but converting them to null before being displayed on any form. This removes the possibility of making a mistake with the two-stage comparison. This requires code to convert the value from infinity to null before displaying the value on the screen, and converting back to infinity before writing to the database, as in the following examples:-
if ($format = $date("31-12-9999")) $format = "" ; change infinity to null
if ($format = "") $format = "31-12-9999" ; change null to infinity
The $format instruction only changes the way the data is displayed on the form. Any operations on the field will always use its unformatted value.
NOTE: Some DBMS's may not support dates as large as 31-12-9999 - check the manual for details and adjust this value accordingly.
Tony Marston
6th June 2000
mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net