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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|