Larry, Thank you for your reply. I'm not sure if it's suitable for the
birthday issue but it's an interesting approach. There are annual tasks
that I'll probably build into this database in the future and I would
incorporate your computed columns into a view.
Doug
Lawrence Lustig wrote:
I have a fairly complex database that uses the date format MM/DD to track items
that have to be done on an annual basis.
If you use this format, R:Base will put the current year on dates as they are
entered. In order to do comparisons you either have to regularize the years in
a standard format, or else semi-julianize them (this is different from
semi-julienning them, which would be done in a food processor).
I have a computed column corresponding to each date column that I may need to
search, defined as ((IMON(DateCol)*100) + IDAY(DateCol)). This produces
numbers like 101 for January 1st, and 1215 for December 15th. These values are
not "packed" (from July 31st to August 1st, for example, is 731 to 801) but
they are in the correct order.
For comparisons, I semi-julianize the variables entered by the user and do a
comparison. You need to take care of "wrap-arounds" -- a request for all dates
between November 1st and January 15th would be WHERE DateCol NOT BETWEEN 116
AND 1031 for instance.
Finally, you must make certain that you are using v79 or better. There was a
very obscure bug prior to that which would allow you to enter very odd dates
into variable edits on forms if you were using DD/MM -- for instance July 90th,
and so on.
I'm sure there are alternatives to this technique -- such as putting the day
and month in separate integer fields -- but I inherited the database using this
technique, and it does seem to work well for the needs of the client.
--
Larry
