Solved Pick Null or Not Null from a form and use in query? (1 Viewer)

mdnuts

Registered User.
Local time
Today, 03:10
Joined
May 28, 2014
Messages
128
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

Immoderate Moderator
Staff member
Local time
Today, 02:10
Joined
Feb 28, 2001
Messages
27,001
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.

 

Micron

AWF VIP
Local time
Today, 03:10
Joined
Oct 20, 2018
Messages
3,476
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

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 03:10
Joined
May 28, 2014
Messages
128
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.
Capture.PNG
 

mdnuts

Registered User.
Local time
Today, 03:10
Joined
May 28, 2014
Messages
128
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

Immoderate Moderator
Staff member
Local time
Today, 02:10
Joined
Feb 28, 2001
Messages
27,001
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

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Jan 20, 2009
Messages
12,849
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
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:
Code:
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

Registered User.
Local time
Today, 03:10
Joined
May 28, 2014
Messages
128
Thanks folks, rewriting the query certainly worked. Not sure why I didn't think of it to begin with.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 03:10
Joined
May 28, 2014
Messages
128
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

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom