search (1 Viewer)

[theDBguy]

when u hit clear and enter new department to search nothing happens it suppose to pull all project numbers for that department

Okay, I see that now. Give me a minute to take a look...

 

[theDBguy]

Okay, I see that now. Give me a minute to take a look...

Okay, here you go. Give it a try and let me know if this is what you want.

 

[jeannier1975]

allow you to enter another search and populate the results in the listbox without shutting the form

 

[theDBguy]

allow you to enter another search and populate the results in the listbox without shutting the form

Yes, that should be what's happening now in the file I posted earlier. Did you try it?

 

[jeannier1975]

it worked can u tell me what i did wrong please

 

[theDBguy]

it worked can u tell me what i did wrong please

Be happy to. In your "dosearch" procedure, you check each searchable area if it is null to build the Where clause in your SQL statement. However, in your "clear" button code, you were assigning an empty string to them. As a result, the IsNull() checks in dosearch were failing and creating a malformed Where clause. Hope that makes sense.

 

[HalloweenWeed]

That's why I never use "IsNull(...)," instead I use a simple custom function:

Public Function ZeroLength(InputVar As Variant) As Boolean
'....................................................................
' This simple function checks the variant, of any type,
'  for either Null or zero-length string.
' Null variants will return True, "zero-length."
' Uninitialized variants will return True, "zero-length."
' If the variant is detected as a 'string,' the variable is then tested for length:
' If the string length is zero, it returns True, "zero-length."
' Otherwise, it returns False, NOT "zero-length."
' Other data types return False by default, as they contain a value (even if zero).
' Created by HalloweenWeed 12/18/2019.
'....................................................................

    If VarType(InputVar) < 2 Then
        ZeroLength = True               'Case: Null or uninitialized
    Else
        If VarType(InputVar) = vbString Then    'Case: String
            If Len(InputVar) < 1 Then
                ZeroLength = True           'Case: Null string
            Else
                ZeroLength = False
            End If
        Else
            ZeroLength = False
        End If
    End If

ExitFunction:
    Exit Function

End Function

(stripped unnecessary error handler)

Now I use (Not) ZeroLength(...) instead.

 

[jeannier1975]

Thank you for your help