test for empty date

  • Thread starter Thread starter mcnewsxp
  • Start date Start date
M

mcnewsxp

i need to be able to determine if a date field is empty or null.
the null part works fine, but i can't get the empty part.
i need this because once the date field has been dirtied it is no longer
null if the user decides to clear the date field later.
i need to know how to do this with sql.
something like:

select * from mytable where ((mydate is null) or (IsEmpty(mydate))

IsEmpty does not work by the way.
 
Dear Mc:

A string/text value can be null or empty, as you are suggestiong.

A date/time datatype cannot. It is a number, actually a floating point
double. When it is not null it has some value, which can be interpreted as
a date and time.

With this in mind, your question does not make sense to me. Do you have
some further explanation?

By a "field" do you mean a column in a table, or a value in a control. A
text box could be used to enter a data, and could be either null or empty.
If you're testing a control, then this makes sense.

If [mydate], your column, is a text datatype, then you can test it:

WHERE Nz(mydate, "") = ""

You could try this to see if it works. If it does, then what you have is a
text value that may seem to represent a date/time value, but it won't sort
or compare as one.

Tom Ellison
 
Is the date field set as a Date/Time data type in the table design? If
you're storing it as a string are you allow zero length strings? If so, then
check for ="". However, I don't recommend storing the date as text if you're
going to use it for sorting or calculations later. If you do, it won't work
properly without you taking extra steps to convert it at that time.

I show that if I clear a date field, the field is once again Null. Are you
checking after the user clears the value in the textbox but before the
record change is saved to the table? If so, then the value hasn't changed.
It will still be whatever date was in the field.
 
it is a date/time field in a table.
the date will be cleared after the record is saved - so it is not null.
 
If [mydate], your column, is a text datatype, then you can test it:

WHERE Nz(mydate, "") = ""
Click to expand...

this returns the same result as is null

this a column or field defined in the table as date/time.
once the field has been dirtied, saved, then cleared it is blank, but not
null and not empty.
 
Dear MC:

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

Tom Ellison


mcnewsxp said:
If [mydate], your column, is a text datatype, then you can test it:

WHERE Nz(mydate, "") = ""
Click to expand...

this returns the same result as is null

this a column or field defined in the table as date/time.
once the field has been dirtied, saved, then cleared it is blank, but not
null and not empty.
Click to expand...
 
Tom Ellison said:
Dear MC:

Just how do you "clear" a date/time value?
Click to expand...

come on, man...

you move your cursor into the field and remove the existing value.
you make it blank.
 
the date will be cleared after the record is saved - so it is not null.

Huh??? What are you doing to "clear" the date AFTER the record is saved? Are
you saving the record again after you clear the date? If not, then the date
is still in the table.
 
Wayne Morgan said:
Huh??? What are you doing to "clear" the date AFTER the record is saved?
Are you saving the record again after you clear the date? If not, then the
date is still in the table.
Click to expand...

come on guys.
i am not that dumb and neither are you...i hope.

got this date field (or clolumn in a table) see.
the user enters a date then saves the record.
the users opens the table or the form that displays this field and takes the
date away/
clears the date. erases the date. blanks the date. date was there but not
now.
got it.
then saves the record.
so where it was once null it is not null or empty.
i can test for null or empty.

so, please help me if you can.
try it yourself and call me nuts.
or try another thread.

tia,
mcnewsxp
 
Dear MC,

Are you using an Access Database table or are you linked to some other data
source?

When you delete the contents of a datetime field in an Access table it
becomes Null. I've never seen it not become null whether I clear the field
with a query, vba code, or just typing directly in the table or in a form
control bound to the field.

So Tom's question was serious. He was seeking more information, since he
probably has never seen the behavior you are describing.
 
As stated in my first message. If the field is defined in the table's design
as a Date/Time data type and I delete a date that was previously there, I
get Null again. Yes, I have tried this to make sure.
 
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 butt 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
 
Are you using an Access Database table or are you linked to some other
data source?

When you delete the contents of a datetime field in an Access table it
becomes Null. I've never seen it not become null whether I clear the
field with a query, vba code, or just typing directly in the table or in a
form control bound to the field.

So Tom's question was serious. He was seeking more information, since he
probably has never seen the behavior you are describing.
Click to expand...

this is an access 2003 table.
it happens to me and to the person who is testing the app for his client.
it happens everytime.
if i reset this date field in this table for all records my query returns
all records.
if i add (type in) dates to 8 records my query returns 8 fewer records.
if i delete/remove/clear out/blank/hilite and press delete/backspace over
the date field/column and rerun my query i still get fewer records that i
originally got.
if i try to get the record from which the date was removed i cannot retrieve
it my using
(MYDATE Is Null) or IsEmpty(MYDATE) in a query.

i can't make it any clearer than this.
all access.
normal table.
normal date/time field/column.
 
please give an example of a query that would retrieve this record where the
date/time field has been subject to this test, if you don't mind.
my query looks like:
SELECT * FROM MYTABLE WHERE ((MYDATE Is NULL) OR IsEmpty(MYDATE)).
i do not get the record that once had a date contained in it, but later had
it removed.
this was brought to my attention by my client and i verified it.
 
i apologize.
i did not realize my words could penetrate skin so easily.

i did try to run the two suggested queries and got exactly the results that
you did.
it is odd in that i tried this on a different copy of the app from the one i
used yesterday which was sent to me by my client.
it looks like i need to see what he is doing that may be causing this.

thanks for your help.

Tom Ellison said:
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 butt 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 said:
come on, man...

you move your cursor into the field and remove the existing value.
you make it blank.
Click to expand...
Click to expand...
 
I am not trying to get involved in this discussion, but you
really should get rid of the IsEmpty in your criteria. It
shouldn't make any difference, but IsEmpty is used to check
if a Variant type variable in a VBA procedure has been
initialized, it has nothing to do with fields in a table.
 
I am not trying to get involved in this discussion, but you
really should get rid of the IsEmpty in your criteria. It
shouldn't make any difference, but IsEmpty is used to check
if a Variant type variable in a VBA procedure has been
initialized, it has nothing to do with fields in a table.
--
Click to expand...
didn't know that.
i had to take it out anyway because it didn't work.
that's what initialized my original question - i wanted to know how to check
for an empty date field/column.
like you can do with a string/character field/column (i.e. <> "" or = ""
or null string).

anyhoot, i am no further along that i was yesterday.
just have to tell my client's users not to enter any dates unless they mean
it.

yuk yuk.

thanks,
mcnews
 
What you have but deleting everything after the word Null and adjusting the
parentheses accordingly.

SELECT * FROM MYTABLE WHERE MYDATE Is NULL;
 
Is this field indexed? If so, have you done a Compact and Repair of the
file. If the index has become corrupted, this may fix it. Access rebuilds
indexes during a compact.
 
What you have but deleting everything after the word Null and adjusting
the parentheses accordingly.

SELECT * FROM MYTABLE WHERE MYDATE Is NULL;
Click to expand...

yes, that's what i have and i have given them a query the will null this
field when required.
 
Back
Top