check for Null or Empty (1 Viewer)
- Thread starter smig
- Start date
Think again.
Using Exit Function like that does not make the code run faster if you use the If Else or ElseIf structures properly.
The remaining comparisions are not included in the execution because they are in the Else section. If each extra If is expressed as an ElseIf within the statements in the group it is very tidy.
Making sure you clean up when you initially write the code is one thing but later when you or someone else makes changes they could easily overlook the "out the window" Exit.
Exit Function from the middle of the code is a very common but bad practice.
Using Exit Function like that does not make the code run faster if you use the If Else or ElseIf structures properly.
The remaining comparisions are not included in the execution because they are in the Else section. If each extra If is expressed as an ElseIf within the statements in the group it is very tidy.
Making sure you clean up when you initially write the code is one thing but later when you or someone else makes changes they could easily overlook the "out the window" Exit.
Exit Function from the middle of the code is a very common but bad practice.
Normaly I don't use Exit Function in the middle of the function but going to an exit tag, and make sure to clean things there.
I find several nested IIFs to be hard to read.
If there is no logic in the nested IIF I tend not to use it.
I find several nested IIFs to be hard to read.
If there is no logic in the nested IIF I tend not to use it.
From this statement:
We've explained and provided code for checking when a field or variable has no value (i.e. Null), when it has a zero-length string and when a Variant (variable) has not been initialised (i.e. Empty) or when an object has not been set (i.e. Nothing).
These concepts are explained in the link provided in post #4. You have to understand what they mean because you seem not to have grasped their meanings.
If you don't want to test for zls, then set the Allow Zero Length property of the field to No. There's not much else code that can be written for what you're after so read the link and re-read all relevant posts.
... I think smig is using the word "No Value" in the wrong context.
We've explained and provided code for checking when a field or variable has no value (i.e. Null), when it has a zero-length string and when a Variant (variable) has not been initialised (i.e. Empty) or when an object has not been set (i.e. Nothing).
These concepts are explained in the link provided in post #4. You have to understand what they mean because you seem not to have grasped their meanings.
If you don't want to test for zls, then set the Allow Zero Length property of the field to No. There's not much else code that can be written for what you're after so read the link and re-read all relevant posts.
here is where you'r wrong vbaInet:
1. it's NOT Null, as I explained at first it's pass the IsNull() as false
2. it's NOT Zero-Length String (ZLS) as trim(string) = "" bring the error
3. it's NOT Empty as it's not variant
it is a NO VALUE
Galaxiom gave me the correct place to look for, I think - a text box on a form with no records or no data in it.
1. it's NOT Null, as I explained at first it's pass the IsNull() as false
2. it's NOT Zero-Length String (ZLS) as trim(string) = "" bring the error
3. it's NOT Empty as it's not variant
it is a NO VALUE
Galaxiom gave me the correct place to look for, I think - a text box on a form with no records or no data in it.
Based on what code and based on what value that was passed to it (as ChrisO has asked twice)?
based on my first post:
I don't send objects to this function, nor booleans.
I'm trying to check data for Null or Empty.
tried to use the IsEmpty function but it return True for Null values
so I made my oun function:
normaly this code works OK.Code:Function NullOrEmpty(strTextToTest As Variant) As Boolean If IsNull(strTextToTest) Then NullOrEmpty = True Exit Function End If If Trim(strTextToTest) = "" Then NullOrEmpty = True Exit Function End If NullOrEmpty = False End Function
but I noticed that it might bring back an error:
2427 - you entered an expression that has no value
this error pop for the If Trim(strTextToTest) = "" Then
which mean it's not null.
how can I send a data with no value ?
I don't send objects to this function, nor booleans.
That's the problem smig, you were using the same structure as before. GalaxiomAtHome has already explained this but I would have thought you had taken out the Exit Function line.
The two lines of code presented in post #6 (and others from ChrisO and lagbolt) indicates how to check for No Value, but it may be confusing to think that Null is also a value. Null is not a value, zero-length string is a value. However, when a field or variable contains zls it may seem that it is not a value, whereas to the Jet engine it is a value. So either one of these functions:
... will return 0 if it has "a value" or -1 if it has "no value". The crucial part about the first function is that highlighted bit, it must be there. For the second function, it is optional but I would advise you include it.
There are three things that can be deceiving - Null, zero-length string and space within a field or variable.
I hope that is clear!
The two lines of code presented in post #6 (and others from ChrisO and lagbolt) indicates how to check for No Value, but it may be confusing to think that Null is also a value. Null is not a value, zero-length string is a value. However, when a field or variable contains zls it may seem that it is not a value, whereas to the Jet engine it is a value. So either one of these functions:
Code:
Function NullOrEmpty(vntTextToTest As Variant) As Integer
NullOrEmpty = (Len(vnTextToTest[COLOR=Red][B] & ""[/B][/COLOR]) = 0)
End Function
Function NullOrEmpty(vntTextToTest As Variant) As Integer
NullOrEmpty = (Nz(vnTextToTest[B][COLOR=Red], ""[/COLOR][/B]) = "")
End FunctionThere are three things that can be deceiving - Null, zero-length string and space within a field or variable.
I hope that is clear!
vbaInet:
I tried both functions, exactly as you wrote them and both gave me an error when I run the report I talked about with no records in it:
the first function gave the error:
run-time error 2427 - you entered an expression that has no value
the second function gave the error:
run-time error -2147352567 (80020009) - you entered an expression that has no value
trying Galaxiom for the IsMissing produced the same error as in your function 2, as it won't find the data as IsMissing
I took lagbolt idea of checking for the data type that cause the error, and it report the data type to be a text box.
I did a reheck and the report query is using any text box data to rely on.
I started to strip the report down, and fund what cause the problem:
using NullOrEmpty(Me.VarName) in one of the Group_Format event.
this Me.VarName is a field on the report.
if I change it to be NullOrEmpty(VarName) where VarName is the variable from the query it report the data type to be Empty (as there are no records)
I know that "Null is not a value, zero-length string is a value"
but in this case it's not even Null, as Galaxiom pointed.
I tried both functions, exactly as you wrote them and both gave me an error when I run the report I talked about with no records in it:
the first function gave the error:
run-time error 2427 - you entered an expression that has no value
the second function gave the error:
run-time error -2147352567 (80020009) - you entered an expression that has no value
trying Galaxiom for the IsMissing produced the same error as in your function 2, as it won't find the data as IsMissing
I took lagbolt idea of checking for the data type that cause the error, and it report the data type to be a text box.
I did a reheck and the report query is using any text box data to rely on.
I started to strip the report down, and fund what cause the problem:
using NullOrEmpty(Me.VarName) in one of the Group_Format event.
this Me.VarName is a field on the report.
if I change it to be NullOrEmpty(VarName) where VarName is the variable from the query it report the data type to be Empty (as there are no records)
I know that "Null is not a value, zero-length string is a value"
but in this case it's not even Null, as Galaxiom pointed.
Ah, I see what you mean. You shouldn't really be checking for No Value, rather you should be checking for an error.
Use something like:
But in this case you should avoid IIF() because that could result in an error.
If it were a subform, then perhaps a record count of the RecordClone would suffice.
Use something like:
Code:
If IsError([Field]) Then
Debug.Print "Has No Value error"
Else
... do something else
End IfIf it were a subform, then perhaps a record count of the RecordClone would suffice.
I can see why my function with the Optional argument failed. In the quick test I used Me.fieldname as the argument. Of course with a variant datatype the function was fed with the textbox object not its value.
Me.fieldname.Value fails with the 2427 error like the others.
(A good reason to avoid Variants if possible.)
I did have a niggling thought later that it should have failed because the error was in the line that called the function.
Funny thing was it got a True result fro the IsMissing the first test. Comes back false now. Maybe some anomaly because I hadn't closed and reopend the form after setting AllowNew to No. That might have affected Access's mood when it decided how to deal with the Variant.
Oh well, at least I was onto the root cause of the error.
Me.fieldname.Value fails with the 2427 error like the others.
(A good reason to avoid Variants if possible.)
I did have a niggling thought later that it should have failed because the error was in the line that called the function.
Funny thing was it got a True result fro the IsMissing the first test. Comes back false now. Maybe some anomaly because I hadn't closed and reopend the form after setting AllowNew to No. That might have affected Access's mood when it decided how to deal with the Variant.
Oh well, at least I was onto the root cause of the error.
Nested IF is not necessary. Use ElseIf.
Code:
If whatever Then
somestuff
ElseIf someothertest Then
someotherstuff
ElseIf anothertest Then
differentstuff
Else
whatever or nothing
End If
Exit FunctionAs soon as it hits a True result it does the appropriate stuff and then Exits with no further tests executed. This is much more readable and concise than either nested Ifs or a series of separate Ifs.
thank you all for the help 
this session was a good learning for me.
I have no problem now as I cancel the report if there are no records in it.
but I sure know now where and how to look for these kinds of errors in future. I couldn't have it without all the help, tip, ideas and info I got here.
not only I can find help for any problem and request on these forums ,also my programming skills improve a lot
this session was a good learning for me.
I have no problem now as I cancel the report if there are no records in it.
but I sure know now where and how to look for these kinds of errors in future. I couldn't have it without all the help, tip, ideas and info I got here.
not only I can find help for any problem and request on these forums ,also my programming skills improve a lot
thank you all for the help
this session was a good learning for me.
I couldn't have it without all the help, tip, ideas and info I got here.
Actually we all come here pretending to help but actually to learn too.
Well not quite, but there is a significant learning element for everyone that contibutes here. The really good stuff comes in the questions we don't realise we needed to ask and the answers we get wrong.
Hi All,
This post helped me to get a fully working validation check, so first off, thank you.
I have another question to ask. I have quite a lot of text boxes that can not be empty. I could simply copy the same code to all of the text box's Lostfocus subroutine however this is very messy and i know there must be a method to reduce the coding. Perhaps by creating a subroutine or function that the text box's LostFocus could 'call'.
Any guidance as to how that could be done?
Thanks.
Dan
This post helped me to get a fully working validation check, so first off, thank you.
I have another question to ask. I have quite a lot of text boxes that can not be empty. I could simply copy the same code to all of the text box's Lostfocus subroutine however this is very messy and i know there must be a method to reduce the coding. Perhaps by creating a subroutine or function that the text box's LostFocus could 'call'.
Any guidance as to how that could be done?
Thanks.
Dan
- Local time
- Today, 03:14
- Joined
- Feb 28, 2001
- Messages
- 27,199
djkay: The question is not whether the textbox is empty, null, or not empty but blank... but what you wanted to do about it. Refuse to allow the change of focus? Pop up a message box? Change the color of the control's background to yellow?
There is nothing at all wrong with the idea of calling a common subroutine that is part of the form's class module but that only makes sense if you always want to do the same thing in every possible control. In fact, if you pass in the control's name (or pass in the control as a control object) and DON'T try to refer to anything else on the form, you can even make it something you could use on multiple forms from a general module. I've done that more than once. The only trick is to remember that "Me.x" doesn't work outside of a class module.
There are two schools of thought here. You can call a subroutine once from each applicable box's .LostFocus event. OR you can wait until the .BeforeUpdate event and run code that loops through the Me.Controls collection looking for controls marked in a certain way (commonly by putting something in the .Tag property) and do whatever you are going to do in a single place via a loop. That's a matter of style.
Side note, and this is NOT intended as a slam: Tacking your question onto someone else's topic sometimes works. More often you do better to start your own thread and merely reference the thread that helped you. If you start your own thread, we see the title and don't recognize it. If you tack onto another thread, we will see the title and perhaps recognize it as something we don't need to touch. We will see it as familiar. If it is marked as "solved," which is the prerogative of the original poster, we might ignore it. And finally, go ahead and open that new thread. Last I checked, we don't charge.
There is nothing at all wrong with the idea of calling a common subroutine that is part of the form's class module but that only makes sense if you always want to do the same thing in every possible control. In fact, if you pass in the control's name (or pass in the control as a control object) and DON'T try to refer to anything else on the form, you can even make it something you could use on multiple forms from a general module. I've done that more than once. The only trick is to remember that "Me.x" doesn't work outside of a class module.
There are two schools of thought here. You can call a subroutine once from each applicable box's .LostFocus event. OR you can wait until the .BeforeUpdate event and run code that loops through the Me.Controls collection looking for controls marked in a certain way (commonly by putting something in the .Tag property) and do whatever you are going to do in a single place via a loop. That's a matter of style.
Side note, and this is NOT intended as a slam: Tacking your question onto someone else's topic sometimes works. More often you do better to start your own thread and merely reference the thread that helped you. If you start your own thread, we see the title and don't recognize it. If you tack onto another thread, we will see the title and perhaps recognize it as something we don't need to touch. We will see it as familiar. If it is marked as "solved," which is the prerogative of the original poster, we might ignore it. And finally, go ahead and open that new thread. Last I checked, we don't charge.
