A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change blanks to 0 (zero)



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 06:08 PM posted to microsoft.public.access.tablesdbdesign
Jodie
external usenet poster
 
Posts: 36
Default Change blanks to 0 (zero)

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?
--
Thank you, Jodie
  #2  
Old March 18th, 2010, 06:24 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Change blanks to 0 (zero)

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.

--
Build a little, test a little.


"Jodie" wrote:

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?
--
Thank you, Jodie

  #3  
Old March 18th, 2010, 06:26 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Change blanks to 0 (zero)

On Thu, 18 Mar 2010 10:08:01 -0700, Jodie
wrote:

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?


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!
--

John W. Vinson [MVP]
  #4  
Old March 18th, 2010, 08:28 PM posted to microsoft.public.access.tablesdbdesign
Jodie
external usenet poster
 
Posts: 36
Default Change blanks to 0 (zero)

I thank you Karl. Apparently, I was not putting a space in between Is and
Null. It is working fine now.
--
Jodie


"KARL DEWEY" wrote:

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.

--
Build a little, test a little.


"Jodie" wrote:

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?
--
Thank you, Jodie

  #5  
Old March 20th, 2010, 04:07 PM posted to microsoft.public.access.tablesdbdesign
Abki
external usenet poster
 
Posts: 2
Default Change blanks to 0 (zero)

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:

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.

--
Build a little, test a little.


"Jodie" wrote:

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?
--
Thank you, Jodie

  #6  
Old March 20th, 2010, 10:26 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Change blanks to 0 (zero)

On Sat, 20 Mar 2010 08:07:01 -0700, Abki
wrote:

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.


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.
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.