criteria expressions when data is removed from a cell

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

If I delete the data from a date field in a form, can I use the expression
Is Null to obtain that record in a query?

What I'm trying to determine is whether the value of that field will be Null
after I delete the data in a form, or whether it will have some obscure
variation like "empty" or "no value" or "zero length string" since data had
existed previously in the field.

Thanks in advance.

Paul
 
A datetime field will contain a date or null. Nothing else.

I do wonder, why you didn't just try this and see what happened. If you had,
you would probably be more likely to remember the outcome.
 
Thanks for the answer, John.

Your question about why I didn't try something as simple as that, to see for
myself, is certainly a fair question. Actually, I did try it, and it seemed
that what you provided in your answer was indeed the case. However, I was
asking that question because I had run into some problems with an update
query that I couldn't understand.

I had written an After Update event procedure that would run an update query
whenever the value of a certain date field changed, provided that date field
wasn't null after it was changed. However, I wasn't getting the results I
expected, and I thought an explanation might be that those values weren't
Null, even if the date was deleted. I was just looking for a deeper
understanding of what might end up in a field where a non-null value was
deleted. For example, I've seen references to a "zero-length string." I'm
still wondering what that is. Is that something different from a value of
Null?

But I've made some progress in my quest to understand this, at least as far
as date fields are concerned, thanks to your message.
 
Good for you. You tested your idea, but wanted confirmation. I can relate to that.

Null is an undefined value. And NULL is never equal to anything including NULL.
Null = Null will return Null which is neither true nor false.

A zero-length string is a string that has NO characters in it, but it is defined
as a string. So, "" = "" will return true. Note there is nothing between the
quotes.
 
This is very interesting. Question: what determines whether a field has a
value of Null in it, vs. when it has a zero-length string in it?

I'm trying to figure out when I have to worry about putting "" in my query
criteria in addition to, or instead of, "Is Null."

And thanks, John, for your replies to my question.
 
In Access (with the JET database engine) -
Date fields and number fields will always have a number or null in them
Yes/No fields will always be True or False
Text and memo fields, can have null, zero-length strings, or multiple
characters strings

Text and Memo fields will be multiple characters or null if you have Allow Zero
Length strings set to False(No). Also, spaces will be stripped.
Linked spreadsheets can contain either

etc.

Hope that helps some.
 
You just turned a light on in a dark room.

That information will solve some problems I had been wrestling with. I'm
glad you mentioned the use of the "Allow Zero Length Strings" property to
remove the ambiguity from text and memo fields.

Very helpful. Thanks, John.
 
Back
Top