Change blanks to 0 (zero)

  • Thread starter Thread starter Jodie
  • Start date Start date
J

Jodie

I need to do an update to change all blank fields to a 0 (zero). In the
criteria, I have tried IsNull and it is not returning any of the blank
records. I have tried IsEmpty and I get a message that say mismatch. What
can I enter in the criteria that will recognize all of the blank fields?
 
What can I enter in the criteria that will recognize all of the blank fields?
There are more than one thing that can appear as a blank. There are nulls
and zero lenght strings.
Use this to pull both --
Is Null OR ""
That is two double quotes.
 
I need to do an update to change all blank fields to a 0 (zero). In the
criteria, I have tried IsNull and it is not returning any of the blank
records. I have tried IsEmpty and I get a message that say mismatch. What
can I enter in the criteria that will recognize all of the blank fields?
Click to expand...

Is Null

The blank is significant. IS NULL is a SQL criterion; IsNull() is a VBA
function. They are confusingly similar in both name and functionality but they
are not identical!
 
I thank you Karl. Apparently, I was not putting a space in between Is and
Null. It is working fine now.
 
Doubble qoutes, null or space are all diffrent.
Double qoutes means the field have data with binary zero. (asci x'00')
Space means field have data as space not binary zero but code X'20' in asci.
Nulls is not represented in data field. Its indicated that field isnt
initiated.

"KARL DEWEY" skrev:
 
Doubble qoutes, null or space are all diffrent.
Double qoutes means the field have data with binary zero. (asci x'00')
Space means field have data as space not binary zero but code X'20' in asci.
Nulls is not represented in data field. Its indicated that field isnt
initiated.
Click to expand...

You're correct about NULL but mistaken otherwise. A text value of "" is an
empty string - a String of zero length. It does not contain a binary zero; and
(unless you go to a good bit of effort) Access will trim trailing blanks, so
you cannot store an x'20' alone in a table field. It will be truncated to an
empty string "" if the field's Allow Zero Length property is true, and to NULL
if it isn't.
 
Back
Top