check for Null or Empty

[smig]

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:

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

normaly this code works OK.
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 ?

 

[DCrake]

change to

If Trim(strTextToTest & "") = "" Then

This will take into consideration both Null values or ZLS's

 

[smig]

thanks,
this will sure make the function simpler

but what the no value mean ?
how can I send a data that is not null, and still has no value ?

 

[vbaInet]

but what the no value mean ?
how can I send a data that is not null, and still has no value ?

Perhaps this write-up from pbaldy will give you some more insight:

http://baldyweb.com/NullEmptyEtc.htm

By the way, IsEmpty() is only used to checked when a Variant variable has been initialised or not. Useful for checking Optional parameters that are, of course, declared as Variant.

 

[smig]

thanks,
still not realy give me an explanation how can I send a variable with no value.
how can I check for No Value ?

 

[vbaInet]

What do you mean? Send a variable in what way?

To check whether the value is Null or zero length string you can do this:

If Nz(theVariable, "") = "" Then

OR

If Len(theVariable & "") = 0 Then

 

[ChrisO]

You could also try: -

Function NullOrEmpty(vntTextToTest As Variant) As Integer

    If Len(vntTextToTest) Then
        NullOrEmpty = IIf(Len(Trim(vntTextToTest)), False, True)
    Else
        NullOrEmpty = True
    End If
    
End Function

Try not to pass back Booleans, they can interfere with non-English language versions of Access.

Chris.

 

[smig]

I'm using what DCrake suggested, and it seems to be OK:
If Trim(strTextToTest & "") = ""

how can I check for No Value ? (t's not null)
isn't a variable get a null value when it declared ?

 

[vbaInet]

You've been provided with different techniques of checking when a field or variable has no value so I don't understand what your question is any longer.

 

[ChrisO]

When a Variant is declared it is set to Empty.

This function will return -1 (True) if vntTextToTest is Null, Empty, ZLS, Nothing or only contains Spaces else it returns 0 (False).

Function NullOrEmpty(vntTextToTest As Variant) As Integer

    If IsObject(vntTextToTest) Then
        If vntTextToTest Is Nothing Then
            NullOrEmpty = True
        End If
    Else
        If Len(vntTextToTest) Then
            NullOrEmpty = IIf(Len(Trim(vntTextToTest)), False, True)
        Else
            NullOrEmpty = True
        End If
    End If
    
End Function

It would help to know what you are passing at the time of the error.

Chris.

 

[smig]

Is Nothing = No value ?

 

[vbaInet]

Null = No Value

Is Nothing is for Objects not variables or fields. It is obvious from ChrisO's code that it checks to ensure it is an object (using IsObject) before testing for Is Nothing. Note a Variant can hold anything, objects; string; number; bool; etc... even Null.

 

[ChrisO]

It would help to know what you are passing at the time of the error.

 

[smig]

That's the point vbaInet:
Null <> No Value

look at my first post:
I send a variable data and it passes the IsNull part with True, and I get the No Value error later.

I use this function all over the application, as you can imagine.

I only saw this error when I tried to create a report that had no records in it. the report's query use this function.
Now I use the NoData event of the report to cancel it.
But I'm afraid to have the same error in other times, likes forms with no records....

 

[MarkK]

Here's an idea...

Function HasNoValue(var As Variant) As Integer
   Select Case TypeName(var)
      Case "Null", "Nothing", "Empty"
[COLOR="Green"]         'true if the item is null, nothing, or empty[/COLOR]
         HasNoValue = True
      Case "String"
[COLOR="Green"]         'true if the string has zero length[/COLOR]
         HasNoValue = (Len(var) = 0)
   End Select
End Function

 

[ChrisO]

It would help to know what you are passing at the time of the error.
The next one will be bold and underlined.

 

[smig]

Chris,
I gave all the info I can
it should be a strings, either declared variables or (as in most cases) fields from tables.

 

[Galaxiom]

If you use the expression NullOrEmpty(Me.controlname) and there is no data in the form you will get the Expression with no value error because Me.controlname has no value, not even a null since there is no record.

 

[smig]

Thnaks,
That's make sence, and it might be the case

is there any way to test for the No Value, other then trapping the error ?

 

[Galaxiom]

You can have the argument in the function as Optional.

Private Function NullOrEmpty(Optional MyArgument as Variant) As Boolean
 
If IsMissing(MyArgument) Then
     NullOrEmpty = True
Else
 
     etc
 
End If
 
Exit Function
 
End Function

BTW Note the use of Else instead of Exit Function.

Exit Function in the middle of the code is like jumping out the window and if the proper Exit includes code necessary to clean up then it will be missed. Exiting like this is a bad habit that should be avoided.

If the condition is true the entire Else section is skipped and the main Exit Function is reached.

Using nested Ifs or ElseIf can accomodate most needs.
If you really do have to exit a function in the middle then it is better to use GoTo to take you to the proper end but this can almost invariably be avoided.