Date Format

[dave h]

Hi,

I have a table with a date field. In my form code I have a query with a
WHERE clause that looks at this date. I want to check that date to see if
it is less than some fixed date (like 1/1/2005) or if it is null/blank If
the date field has never been initialized to any value, then it does not
return that row. So, for example, dateTable has 3 rows. 2 rows have dates
less than 1/1/2005. The third row has never had a date entered. If I run
this query: SELECT mydate FROM dateTable WHERE mydate < #1/1/2005#, I only
get 2 rows returned.

I can set the default for the date field to 1/1/1000 and everything works
fine except the user doesn't like to see this date being displayed in list
boxes where I can't intervene and change 1/1/1000 to blank (unless there is
some clever SQL that would do this).

So, how can I select those rows that have this date field that has never
been initialized? Or how can I change a default value like1/1/1000 to a
blank inside of an SQL statement?

Thanks

 

[John Vinson]

So, how can I select those rows that have this date field that has never
been initialized? Or how can I change a default value like1/1/1000 to a
blank inside of an SQL statement?

I'd just use

< [Enter date:] OR IS NULL

on the criteria line. The OR IS NULL will find those records where the
date field is empty. I'd recommend against storing a phony date for
the purpose.

John W. Vinson[MVP]

 

[dave h]

Thanks John, that does it - much appreciated!

So, how can I select those rows that have this date field that has never
been initialized? Or how can I change a default value like1/1/1000 to a
blank inside of an SQL statement?

I'd just use

< [Enter date:] OR IS NULL

on the criteria line. The OR IS NULL will find those records where the
date field is empty. I'd recommend against storing a phony date for
the purpose.

John W. Vinson[MVP]