Re: test for empty date

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Dear MC:

I am dealing with reality here in trying to help you. As Wayne said, doing
this makes the value of the subject column NULL. Your post indicates that
the value involved is not NULL. I was trying to find out what it really is,
as that's the only way I could help you.

Being reasonably courteous with those who endeavor to help you is a skill
from which I believe you would benefit.

I do not know of any "value" (NULL is not a value) that could be in a
datetime column what will appear as "empty". This occurs only with text
types.

If everything you have said is correct, then we have an anomaly here, and
need to try to work with you to find out just what that is. As unpaid
volunteers, this actually demonstrates a dedication that, if that's all
there was, should command a minimal degree of polite respect. This should
be a minimum standard if you wish to gain the assistance you may want.

I have tried to reply here without excessive scolding, or anything else
approaching that. Even in giving you advice on behavior, it is still my
desire to be helpful.

As I have sometimes done, I can *** out and let others try to help you, if
they wish. However, I do not believe my technical advice has been
incorrect. Rather, it has been directed to uncover first of all the most
likely cause of your difficulty.

I recommend you write a couple of queries to test this. I just did so
myself to test my ideas. I put 3 rows in a table. I put values of [Date]
in 2 of them, leaving the other row without entering a value for [Date].
Here's what I queried:

SELECT [Date] FROM TableName WHERE [Date] IS NULL

This returned one row, as expected.

I selected one of the rows with a date in it and deleted that. The query
now returns 2 rows. The one where the date was deleted is not null in that
column.

SELECT CDbl([Date]) from TableName

This returns #Error for rows with NULL in them, matching those rows that are
returned by the previous query. Those that do have a value in them show up
as numbers. I suggest this because, if you have values in your table where
this date is not null as a result of deleting the date, as you suggest, then
there is no alternative but that there must be some value in the column, and
this would reveal something about that value. This is a theory I'm
advancing that may be diagnostic of your problem, based on the assumption
that all the information you have provided is correct, and that you really
are experiencing something I cannot explain by all my experience with such
things.

I hope you will find this useful, but I cannot predict that it will do so.
It will be helpful only if you choose to implement it carefully and report
the results accurately. If you are impatient and do not reply carefully and
accurately then no one will learn anything. While you're at it, please
review the earlier questions and suggestions and make sure you are accurate
in how you have replied there, too.

Tom Ellison


"mcnewsxp" <mcourter@xxxxxxxxxxxxxx> wrote in message
news:ejbGazNOGHA.3196@xxxxxxxxxxxxxxxxxxxxxxx

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:eC$Lf7MOGHA.3788@xxxxxxxxxxxxxxxxxxxxxxx
Dear MC:

Just how do you "clear" a date/time value?


come on, man...

you move your cursor into the field and remove the existing value.
you make it blank.




.

Quantcast