Set field to Null instead of zero length string

[smercer]

Hi all

Is there a way to make a textbox (or other control) set to null from visual basic?

At the moment when I want to delete a value from a field, I have to code like this:

txtTextBox.value = ""

and then when I want to check for null value I have to do it like this:

if txtTextBox.value <> "" or Not IsNull(txtTextBox.value) then

I find this very messy coding, and hard to make the coding to work properly.

Is there a better way of doing it like this?

Thanks in advance

 

[ghudson]

The NZ() function is another option.

I prefer to check for nulls and empty stings exactly the way you listed above.

The 1st part tests if a field is empty but not actually Null because a user might have deleted the contents of the text box which means you can not use the IsNull test because that would be false. The 2nd part tests if the field is untouched and empty.

I just noticed the way you are checking for Nulls. You have it backwards if you really want to ensure the field is not Null or an empty string. Try this...

If txtTextBox = "" or IsNull(txtTextBox) Then
   'True
   MsgBox "It is empty"
Else
   'False
   MsgBox "It is not empty"
End If

 

[smercer]

ghudson: Thanks for helping, but I don't want to check if they are either (as in plural) is null or is a zero length field. what I want to do is set the field to null instead of a zero length field so that I only have to test it for being null only because that is access's default way of leaving a field blank. and I just want to return a field back to what it was.

 

[EMP]

    txtTextBox.Value = Null

 

[smercer]

    txtTextBox.Value = Null

Thanks EMP, That is just what I whated, and I appreciate your help

 

[RoyVidar]

The question seems resolved, but I take the liberty of popping in and issue a little warning, and some suggestions.

Often I think "What If...", and in this case, are you 100% sure the controls will never contain "", only Null if there are no values? What if you at a later point of time decide to assign a string variable to a control? Strings cannot be Null, so if they are used, they "instantiate" as "", so when assigning "no string" to the control, it might become "", then what... (or forget assigning Null)

I find it usefull to test for both conditions, but use some of the tests below (there are more alternatives too). They will test both conditions. If I for some reason should need to perform differently if "" vs Null, then I'd use two tests.

if len(me!txtBox.value & vbNullString)=0 then
- concatenate the text control with "", then test for the length effecitively tests both conditions

If len(trim$(me!txtBox.value & vbNullString))=0 then
- adding the trim function to remove leading/trailing spaces, which can be important when testing the .Text property of controls in for instance the on change event...

If trim$(me!txtBox.Value & "")="" then
- probably the fastest test, testing both Null and "".

Now, concatenation is an expensive operation in terms of recourses (if one need to test many controls/fields), but I think the above tests would still be faster than using two tests (and more reliable than relying upon controls never to contain "").

- so be careful

 

[smercer]

The question seems resolved, but I take the liberty of popping in and issue a little warning, and some suggestions.

Often I think "What If...", and in this case, are you 100% sure the controls will never contain "", only Null if there are no values? What if you at a later point of time decide to assign a string variable to a control? Strings cannot be Null, so if they are used, they "instantiate" as "", so when assigning "no string" to the control, it might become "", then what... (or forget assigning Null)

I find it usefull to test for both conditions, but use some of the tests below (there are more alternatives too). They will test both conditions. If I for some reason should need to perform differently if "" vs Null, then I'd use two tests.

if len(me!txtBox.value & vbNullString)=0 then
- concatenate the text control with "", then test for the length effecitively tests both conditions

If len(trim$(me!txtBox.value & vbNullString))=0 then
- adding the trim function to remove leading/trailing spaces, which can be important when testing the .Text property of controls in for instance the on change event...

If trim$(me!txtBox.Value & "")="" then
- probably the fastest test, testing both Null and "".

Now, concatenation is an expensive operation in terms of recourses (if one need to test many controls/fields), but I think the above tests would still be faster than using two tests (and more reliable than relying upon controls never to contain "").

- so be careful

Thanks Roy, I'll keep that in mind.

 

[ghudson]

ghudson: Thanks for helping, but I don't want to check if they are either (as in plural) is null or is a zero length field. what I want to do is set the field to null instead of a zero length field so that I only have to test it for being null only because that is access's default way of leaving a field blank. and I just want to return a field back to what it was.

txtTextBox = Null

That will give the field a Null value but what if the user starts to type something into the txtTextBox field and then decides that they did not want to do that and they use the delete key to remove the keyed values in the txtTextBox field?

The field will look empty but it will not be Null !!!

You are covering your bases if you continue testing with what you were doing since that will catch the nulls and empty strings.

If txtTextBox = "" or IsNull(txtTextBox) Then

Just my humble opinion of course but I like to trap for the unexpected for you never know what a user will do.

 

[ahuvas]

Im not nearly as experienced as anyone here but Ill tell you one thing I have learnt very quickly - never code as if you are trying to meet the needs of your most experienced user - but for your least experienced user. They are always going to do something you never could imagine could even be done causing you major problems in the process!

 

[Brianwarnock]

You are so right Ahuvas, it is propramming for the error situations that is the most important, I remember back in the 60's we used to say that we could test for weeks but the clerks out in the office could break the system in 10 minutes.

Brian

 

[GolfProRM]

Question - couldn't you just use

If Nz(txtTextBox, "")= "" Then
   'True
   MsgBox "It is empty"
Else
   'False
   MsgBox "It is not empty"
end if

This would convert a null to "" and then compare it with "", so if the field is either Null or "" it would check out.

Ryan

 

[tehNellie]

Where I don't want NULL values that's how I test, it just simplifies the test code and makes it easier to read imo.

 

[Deeramaha]

    txtTextBox.Value = Null

Thanks EMP, I had the same concern and your solution was so so helpful

 

[Cronk]

Brianwarnock

My take on that is
"Make something fool proof and the world will invent a better idiot."

 

[BlueIshDan]

If Len(txtBox.Value & vbNullString) > 0 Then