vbNull and vbNullString

[smig]

Tried to put a vbNull into a string type field (Using a recordSet routine) and I got 1 as result.
Tried vbNullString and got an empty string field (""), though in the Help they say it will result as 0

Is there a way to put a Null into the field, or should I simply stick to "", or maybe vbNullString is better ?

 

[llkhoutx]

I generally use "" to store into a field.

 

[smig]

I generally use "" to store into a field.

Me too. But this is not null.
I also found that trying to put vbNull into number format field will result as 1, not Zero.

Is there any way to put nulls?

 

[Frothingslosh]

As far as I'm aware, you cannot assign vbNull as a value in any saved field - in Access, you HAVE to use either 0, an empty string, or some value you've set to represent 'no value'.

It's kind of odd, as manually deleting a value is basically the same as inserting a null value, but there you go.

 

[The_Doc_Man]

vbNull is the ASCII character NUL (just like vbCRLF is a carriage-return and line-feed sequence of characters). vbNullString is an empty character sequence that (should) compare as equal to "" (the empty string).

The idea of a NULL is not the same as a vbNull. As crazy as the sentence sounds, vbNull is not NULL. It is a non-printing but extant character.

A null occurs (for example) when you have a JOIN between two tables and the child table is sparse with respect to the parent and you have chose to always return the parent record regardless of the existence of a child record. In the case where the parent exists but is childless, the fields that WOULD have been returned from the child record are NULL, which does not mean the same thing as vbNull.

You could test for a field to be NULL using the IsNull function (returns T/F). However, if you tested vbNull for IsNull, it isn't NULL so you get back FALSE. If you tested vbNull with IsEmpty, it isn't - but vbNullString should be empty.

Confusing? Of COURSE it is confusing. We ARE talking about a Microsoft product, aren't we?

 

[smig]

I know what null is.
I asked if there is any way to set a field to null, either a string or numbe. Seems not.
But getting 1 when you put Null is realy surprising for me.

 

[jdraw]

According to Allen Browne
"Only variants can contain the values Null, Empty, or Error. "

 

[The_Doc_Man]

jdraw, with due respect to Allen, I think that is perhaps misleading. If that were true, I would not be able to have a TRUE result for an IsNull([x]) where [x] is a proper string with a non-variant type. If [x] is a field from a child table and happens to correspond to a childless parent record, that IsNull works.

Now, I'll agree that for variables in code, that statement might be true - though again, I've seen empty strings in code. So I'm not sure what he is saying there, but I tend to think there might be either exceptions or alternate viewpoints.

 

[Frothingslosh]

That IsNull works not because field [x] is a string containing a null, but rather because there is no value at all assigned to [x], which is precisely what a 'null' is. Conceptually, it's not a value, but rather a lack of any value at all.

 

[smig]

That IsNull works not because field [x] is a string containing a null, but rather because there is no value at all assigned to [x], which is precisely what a 'null' is. Conceptually, it's not a value, but rather a lack of any value at all.

That is true.
My question is how do you put a lack of value into a field, or in other words how do you remove any value from a field, without deleting the entire record ?

 

[Frothingslosh]

That is true.
My question is how do you put a lack of value into a field, or in other words how do you remove any value from a field, without deleting the entire record ?

You know, I was thinking about that myself the first time I ran across this thread. That is PRECISELY what hitting the 'DEL' key does with the field highlighted, after all, so it should be able to be replicated programattically.

You can probably do it with an update query:

UPDATE TableName SET TableName.FieldName = Null WHERE Conditions

(Air code, obviously not tested, yadda yadda)

 

[jdraw]

I haven't tested this, but I did some google_ing and found this

http://p2p.wrox.com/access/42740-easy-question-setting-variable-null.html

One Poster said he tested it. Let us know if it is applicable.

 

[Frothingslosh]

Ah, yeah, Me.txtFieldName would almost certainly do the trick on a form!

I really need to stop jumping to more complicated solutions! :banghead:

 

[gemma-the-husky]

What exactly are you trying to set to null?

You can set a field in a table ( therefore a bound control ) to null. You can set a variant type variable to null. You can't set any other variable to null.

 

[Galaxiom]

vbNull is the ASCII character NUL

No it isn't. vbNull is a Long with a value of 1.

It is probably the most misunderstood constant in all of Visual Basic.

It is a variable type constant like vbEmpty (0), vbInteger (2), vbLong (3) etc.

It is meant to be used llike this:

? vartype(Null) = vbNull

BTW The NUL character (ASCII 0) is vbNullChar and can be seen as such with:

? Asc(vbNullChar)

 

[smig]

No it isn't. vbNull is a Long with a value of 1.

It is probably the most misunderstood constant in all of Visual Basic.

It is a variable type constant like vbEmpty (0), vbInteger (2), vbLong (3) etc.

It is meant to be used llike this:

? vartype(Null) = vbNull

BTW The NUL character (ASCII 0) is vbNullChar and can be seen as such with:

? Asc(vbNullChar)

Thanks for this clarification
This make sense. Well, its not but it does explain what I got.

I use recordset and not form so I cant use me.Myfield = null
I will try to use Null in the recordset and see what I get.

Thank you all

 

[gemma-the-husky]

smig. the way you describe your query is what needs clarifying. What do you mean by field?

if you mean a control on a form, or a field in a table, then you are dealing with a complex object, and you can assign [mycontrol] = null (not vbnull, which galaxiom explained is something different)

if you mean a variable, then you cannot assign null to any variable, because variables are typed, and any value has an assigned meaning. The exception to this is a variant, and I presume that a variant is once again a complex object, rather than a true variable,

vbnullstring is a constant that returns whatever constitutes a zero length string.

saying

mystringvar = vbnullstring is tantamount to saying mystringvar = ""

Except that if access/vba was to decide to store strings using a different paradigm, (eg c strings, and pascal strings are structured differently. Are vba strings the same as c strings?) then the assignment using vbnullstring would still work, (because MS would fix vba to make sure it did still work), but a direct assignment conceivably might not.

 

[smig]

I wrote on first post that im trying to set it into a table using a recordset.

 

[gemma-the-husky]

well you should be perfectly ok with this syntax

rst!somefield = null

 

[smig]

Thank you

So what do we have vbNull and vbNullString for ?