IsNull clarification

[Jen0dorf]

Hi

building a query to update a field called notes with a format of long text

I have built the update query which works (as per previous help from forum)



I only want to update the records where the exisiting Notes field is empty,

So I was thinking of using the criteria "IsNUll" in the update query for the field Notes.

But searching google seems to show that IsNull only finds a lack of viable data not an empty field. I'm assuming that viable data in a long text field means any text or number will register as viable and therefore that field will not be updated

Am I correct



thanks

Ian

[June7]

Use Is Null, not IsNull().

Review

http://allenbrowne.com/casu-12.html

http://allenbrowne.com/QueryPerfIssue.html

An empty string is not the same as Null. I don't allow empty strings saved in my text fields.

However, can handle both with:

[fieldname] & "" = ""

[orange]

Further to June's advice, use a select query with the appropriate IS NULL criteria to ensure you are selecting the proper records. Double check before doing the UPDATE query. Updates can be quite unforgiving if you mess up the criteria.
Good luck.

[Jen0dorf]

hi

Thanks

Ian

[Jen0dorf]

Hi

"An empty string is not the same as Null. I don't allow empty strings saved in my text fields.

Do you mean make it a required field? if so how do you cope with a field designated nee ( ie pre marriage name) if the person has never been married?

thanks

Ian

[orange]

Ian,

From the computer perspective, an empty field can mean:
-NULL
-a zero length string (ZLS)
-and in some cases people consider a blank or space to be empty.

A NULL is undefined/unknown.
A ZLS is a string with no length.
A blank or space is a character.

If you set a field's required property to Yes, then you must have a value.

ISNull is an vba function.
IS NULL is an SQL expression

For more info:
Errors with NULL

IsNull

SQL IS NULL
Null and IsNull() differences

Good luck.

[June7]

I set text field AllowZeroLength property to No. A field can be Null but cannot have empty (zero length) string.