Set field to Null instead of zero length string (1 Viewer)

smercer

Registered User.
Local time
Today, 14:57
Joined
Jun 14, 2004
Messages
442
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:

Code:
txtTextBox.value = ""

and then when I want to check for null value I have to do it like this:
Code:
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

Registered User.
Local time
Yesterday, 22:57
Joined
Jun 8, 2002
Messages
6,199
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...

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

smercer

Registered User.
Local time
Today, 14:57
Joined
Jun 14, 2004
Messages
442
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.
 
Last edited:

EMP

Registered User.
Local time
Today, 03:57
Joined
May 10, 2003
Messages
574
Code:
    txtTextBox.Value = Null
 

smercer

Registered User.
Local time
Today, 14:57
Joined
Jun 14, 2004
Messages
442
EMP said:
Code:
    txtTextBox.Value = Null

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

RoyVidar

Registered User.
Local time
Today, 04:57
Joined
Sep 25, 2000
Messages
805
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

Registered User.
Local time
Today, 14:57
Joined
Jun 14, 2004
Messages
442
RoyVidar said:
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

Registered User.
Local time
Yesterday, 22:57
Joined
Jun 8, 2002
Messages
6,199
smercer said:
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.
Code:
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.

Code:
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

Registered User.
Local time
Yesterday, 19:57
Joined
Sep 11, 2005
Messages
140
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

Retired
Local time
Today, 03:57
Joined
Jun 2, 2003
Messages
12,701
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

If all else fails, hit F1
Local time
Yesterday, 21:57
Joined
Feb 6, 2008
Messages
61
Question - couldn't you just use
Code:
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

Registered User.
Local time
Today, 03:57
Joined
Apr 3, 2007
Messages
751
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.
 

Cronk

Registered User.
Local time
Today, 14:57
Joined
Jul 4, 2013
Messages
2,435
Brianwarnock

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

Users who are viewing this thread

Top Bottom