Tired of using double protection

Hi all,

I believe prevention is the best cure, but what I'm doing is overkill I think.
I have an Insert command button that is enabled AFTER this combo is updated.

BUT, I know somewhere down the line a user may clear the combo, making it null, which will in turn cause an invalid use of null error message.


The reason I've developed a habit to do the two IF's above, is in my previous project, I used just the top IF statement that worked fine for a while, then I changed it to the bottom IF statement which worked fine for only some time.

In the end I've had to use both because maybe im going crazy, sometimes one wont work for all situations.

Am i doing something wrong?

You are not doing anything wrong at all. You are actually testing for two different conditions, a null and a zero length string. You can put all of this into one if statement using the following to check for both conditions in one line ...


Here if it is a null, it is caught by Nz and it pretends that it is a zero length string - if not, it leaves as is (a zero length string or some data). Then it tests the length of it and will catch the zero length string (and by substitution - the null).

-dK

Null does not equal an empty string ("")
actuall null doesnt equal anything not even Null itself, the only way to detect it is using the Isnull function or "Is null" in SQL.

One way around the double check is
If IsNull(cboSelectTrade.Value) or cboSelectTrade.Value = "" Then
or use the NZ to replace/fill the null with an empty string
If NZ(cboSelectTrade.Value) = "" Then

Actually doing the Isnull() or = "" thing is quite common... and I think is considered to be the "best way" of taking care of this problem.

Good luck

Edit: Cross posted with DKinley

thanks again all

just use this:

or

this does the same trick, take the one you need

vinzz, your solution does not handle both null and ZLS.

1jet - here is one last suggestion:

If Me.SomeField & "" = "" Then
The field is empty or null
Else
It is not
End If

G’day 1Jet.

You could also try: -


Regards,
Chris.

And to chime in, my favorite is dkinley's one:

Well, we’ve got plenty of choices.

Plenty of choices indeed!
Just to mention a couple of things if I may.

To come back to Vinzz's suggestion at least partially...
The first of the two offered statements is a valid test.
If Me.avalue <> "" Then
As Null is returned from any operator comparison of Null with anything - and since Null is not True (or anything else for that matter ;-) then that expression is able to determine a valid value for a ZLS and also Null. i.e. it's a valid test for an actual value.
The second suggestion however
If Not Me.avalue <> "" Then
is not valid. Not Null is still Null. So you can't expect the converse of the earlier statement to work in the event of Null. The earlier statement was relying on Null's behaviour and that we're expecting an expression which evaluates to True.
Taking the converse has not flipped a False to True. It's flipped a Null to Null.
(Hmm - I'm saying "Null" too often now aren't I. :-s)

Of course an If statement is designed to execute the Else clause under whatever circumstance doesn't see the previous comparisons met - so you'd have to use that to catch the converse here. i.e.


The other point was just to make clear that this has all been in VBA.
We're free to massage values from Null to a ZLS, e.g. Len(Nz(Me.cboSelectTrade, "")) and the ever popular Me.SomeField & "", for a subsequent comparison against a value (zero or ZLS) and expect almost no side effect. (A tiny processor calculation).
We should look to be a bit more careful when considering the same concept in a query against your database though.
Of course, if you've designed the database yourself and know that you have prevented ZLS in your fields - then you're free to simply compare for Null.
Even then, in a query this should be achieved through
FieldName Is Null
or
FieldName Is Not Null
rather than
IsNull(FieldName)
or
Not IsNull(FieldName)
Because of the inherent overhead in making a function call upon a field in a query - and subsequently using that to apply criteria upon... added (notably) to the loss of any optimisation which would have been provided by an index present upon that field.

OK - suppose you haven't disallowed ZLS in your tables (Eurgh ;-).
Then you do need to compare for either Null or ZLS.
Tempting though it is to revert to the VBA methods - again you'll lose that optimisation.
FieldName Is Not Null And FieldName <> ""
may seem like an unnecessary mouthful compared to
Nz(FieldName, "") <> ""
but the database engine isn't awarding prizes for brevity.
(Note: Also avoiding "OR" conditions in your criteria is more likely to get you a better optimised execution plan.)
However if we come back to the start of my post - and support for that initial expression, in a query you should receive a very beneficial optimisation in checking for a valid value with just
FieldName > ""
Which is also handling Nulls (for the reasons stated earlier) - but the DB engine considers it favourably.

Cheers!

nice explanation!

My second suggestion won't work indeed, i was a bit too fast.