Solved Pick Null or Not Null from a form and use in query?

[mdnuts]

Good afternoon.

I've got a query that pulls values that are sometimes null and other times not. What I'd like to do is on a form have the user select
- Show only not null items
- Show only null items
- Show all items.

and use that choice in a query. My feeling is if I can pass Null, NotNull or * to the query it would suffice? But how?

I can easily have a drop-down on the form with possible selections of Show/Not Show/All with values of Null/NotNull/* but if I use the query criteria in that field of [forms].[myform].[cboValue] it returns no records at all.

Any ideas?

 

[The_Doc_Man]

It is going to depend on how you form the query. Are you building this query dynamically? How many fields are potentially selected?

The SQL syntax would be to place either an ISNULL(FieldX) or a NOT ISNULL(FieldX) or have no criteria for FieldX in the WHERE clause.

IsNull Function - Microsoft Support

Syntax: IsNull(expression)

support.microsoft.com

 

[Deleted member 147267]

or have no criteria for FieldX in the WHERE clause.

Or if in vba, build a dynamic where clause based on whether or not certain fields have data.

 

[theDBguy]

Hi. Just a thought, but maybe something like?

...WHERE (FieldName Is Null AND Forms!FormName.ComboName="Show") OR (FieldName Is Not Null AND Forms!FormName.ComboName="NotShow") OR Forms!FormName.ComboName="All"

 

[mdnuts]

Thank you Doc.

Not that fancy, (adding a screenshot of the Query design and the form where the value would be selected from. The "Via" field is where I want to have the criteria based on the form frmSelect cboType field.

 

[mdnuts]

Thank you Doc.

Not that fancy, (adding a screenshot of the Query design and the form where the value would be selected from. The "Via" field is where I want to have the criteria based on the form frmSelect cboType field.
View attachment 80501

... although I suppose I could do an event that rewrites the query's SQL..?

 

[The_Doc_Man]

I'm guessing that your "Select Inheritance" box is the one that is relevant to the question? Yes, what I would do is once you selected one of the options in the "Select Inheritance" box, I would then immediately rewrite the SQL. You would only need to rewrite the WHERE clause, so you could easily make a pre-defined constant string that is the front part of the query, in specific the SELECT and FROM clauses, and tack on whatever you needed for the WHERE clause.

 

[Galaxiom]

The SQL syntax would be to place either an ISNULL(FieldX) or a NOT ISNULL(FieldX) or have no criteria for FieldX in the WHERE clause.

IsNull() is a VBA function that one normally uses to check for Null in circumstances such as checking for nulls as values read from a recordset.

In SQL it is better practice to use the native SQL expressions:
FieldX Is Not Null
and
FieldX Is Null

 

[The_Doc_Man]

I defer to G's advice.

 

[arnelgp]

put the items on your cboType combo:

Show only not null items
Show only null items
Show all items

remove the criteira from your Query and save it.

add code to the AfterUpdate Event of cboType combobox:

Private Sub cboType_AfterUpdate()
       
    Dim qd As DAO.QueryDef
    Dim sSQL As String
   
    sSQL = "SELECT P_acronym, CCI_Number, Inheritable_Status, Via From yourTableNameHere"

    Select Case Me.cboType.ListIndex
        Case 0     'show only not Null items
            sSQL = sSQL & " Where Not (Via Is Null);"
           
        Case 1      'show only Null items
            sSQL = sSQL & " Where (Via Is Null);"
    End Select
   
    'close the query if already open
    '
    If SysCmd(acSysCmdGetObjectState, acQuery, "yourQueryNameHere") > 0 Then
        DoCmd.Close acQuery, "yourQueryNameHere"
       
    End If
   
    'ListIndex = -1 means the combo is blank
    'therefore, we don't want to show the query
    'if this is the case
    If Me.cboType.ListIndex > -1 Then
       
        Set qd = CurrentDb.QueryDefs("yourQueryNameHere")
       
        'assign the new SQL string
        qd.SQL = sSQL
        Set qd = Nothing
       
        DoCmd.OpenQuery "yourQueryNameHere"
       
    End If
End Sub

 

[mdnuts]

Thanks folks, rewriting the query certainly worked. Not sure why I didn't think of it to begin with.

 

[theDBguy]

Thanks folks, rewriting the query certainly worked. Not sure why I didn't think of it to begin with.

Hi. Glad to hear you got it sorted out. Would have been nice to know if my suggestion worked or not. Good luck with your project.

 

[mdnuts]

Hi. Glad to hear you got it sorted out. Would have been nice to know if my suggestion worked or not. Good luck with your project.

In a manner of speaking. I ended up needing to rewrite the query SQL. End of the day it looked very similar to what arnelgp put.

 

[theDBguy]

In a manner of speaking. I ended up needing to rewrite the query SQL. End of the day it looked very similar to what arnelgp put.

Hi. But my question was, did you end up using code or just a query? If code, then I was wondering if the query worked or not. That's all.