Solved Handling Null Values from a Listbox in a Query

[Bean Machine]

Hi All,

So I am unsure how I should start this question off, but I am having trouble with handling null values from a listbox. I want the query to check what value is stored in the lsitbox and then only show results for that value. In the past I have used a "janky" method as seen here: Like "*" & [Forms]![frm_PLIS]![lst_AttS] & "*". This method will not work with my listbox, however, as using Like "*" and having the listbox value as "1" would present me with values for "1", "11" and so on. I would prefer not to use an or statement as that has presented me with issues in the past. Any support would be great. Attached is an example of the listbox values and an example of the current way I am trying to get it to work.

 

[Minty]

Use the following syntax

= [Forms]![frm_PLIS]![lst_AttS] OR [Forms]![frm_PLIS]![lst_AttS] is Null

This will return all the records if the list box value is null

 

[The_Doc_Man]

The question is "How do you tell one null from another?" (Answer: You can't.) What value is there in allowing nulls in the list in the first place? More to the point, if you can have nulls in that column, what does it REALLY mean? To me, the column being displayed is the wrong choice OR has not been properly filtered or defined. The best way to handle nulls is to exclude them from selection in the first place.

So... what is the data source for that column?

 

[Bean Machine]

The question is "How do you tell one null from another?" (Answer: You can't.) What value is there in allowing nulls in the list in the first place? More to the point, if you can have nulls in that column, what does it REALLY mean? To me, the column being displayed is the wrong choice OR has not been properly filtered or defined. The best way to handle nulls is to exclude them from selection in the first place.

So... what is the data source for that column?

So I want to be able to have information still shown if the user does not actually select anything from the list. The reason for this is because I have a bunch of different filtering comboboxes/listboxes etc. that the user can choose from to limit data as they see fit. What would you suggest the best way around this would be? Attached is an example of how my form looks. The graphs are built using the data from the filtered query when a user inputs, lets say, Term: "1222" for example. There is likely a much better way of doing this which is why I am asking you experts heh.

 

[MajP]

This shows a very easy way to build a search form with many different search controls.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[The_Doc_Man]

When synthesizing a search string or a WHERE clause or filter string, when there is a NULL you don't put anything in the string that would limit your return choices. In theory you could have NOTHING in ANY of those items on your form, and if you build the selection or filter string correctly, you would just get back all of the records unfiltered.

The thread that MajP posted has a long discussion in multiple parts regarding dynamically building and applying search strings. In that, it is responsive to your questions on how to approach the problem as you have described it. It will take a bit of reading, but it appears to mostly handle anything you are likely to encounter.

 

[Pat Hartman]

As an overview, Your picture is of a complex search form. Rather than have a fixed WHERE clause, it is probably better to build one on the fly. As you loop through the controls, you would build the where clause using ONLY controls where the selection is Not Null.

 

[Minty]

As Pat says - for a simple one or two criteria filter, the method I showed you is fine, and works efficiently, however with the number of search terms you have it becomes quite unwieldy.

Much better to build it dynamically based don't the selections made.

 

[MajP]

however with the number of search terms you have it becomes quite unwieldy.

The code module I provide makes this easy. Example this is much more complicated than the OPs. It includes multi value listboxes, to from dates. In fact could add as many controls as I want (combos, options, listboxes, text boxes, dates, strings, booleans). Each new control is a single line of code. You can see the filter in the bottom textbox.

Here is the entire code (using the provided modules)

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String

  'Need final filter
  Dim StrFilter As String
  Dim AndOr As CombineFilterType

  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select

  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
  StrFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)

  GetFilter = StrFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
End Function

Building that functionality by scratch would be 20 times the code.

@Bean Machine if you post that db I can build the filter form. Make sure every control has a descriptive name like "cmboInstructor" not "combo1"

 

[Bean Machine]

however with the number of search terms you have it becomes quite unwieldy.

The code module I provide makes this easy. Example this is much more complicated than the OPs. It includes multi value listboxes, to from dates. In fact could add as many controls as I want (combos, options, listboxes, text boxes, dates, strings, booleans). Each new control is a single line of code. You can see the filter in the bottom textbox.

View attachment 108423

Here is the entire code (using the provided modules)

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String

  'Need final filter
  Dim StrFilter As String
  Dim AndOr As CombineFilterType

  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select

  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
  StrFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)

  GetFilter = StrFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
End Function

Building that functionality by scratch would be 20 times the code.

@Bean Machine if you post that db I can build the filter form. Make sure every control has a descriptive name like "cmboInstructor" not "combo1"

Hi! First of all I just want to say thank you so much for all of the great and informative information you have provided me. Going to spend some time brushing up on things. I will make a copy DB and send it as soon as I can with bogus data in the fields as it currently houses sensitive information. Thank you once again!

 

[Isaac]

As Pat says - for a simple one or two criteria filter, the method I showed you is fine, and works efficiently, however with the number of search terms you have it becomes quite unwieldy.

Much better to build it dynamically based don't the selections made.

Minty! I think I am a detective and may have just been clever.
Do you live in the UK, and were you on a Phone using speech to text when you posted this?

 

[Bean Machine]

however with the number of search terms you have it becomes quite unwieldy.

The code module I provide makes this easy. Example this is much more complicated than the OPs. It includes multi value listboxes, to from dates. In fact could add as many controls as I want (combos, options, listboxes, text boxes, dates, strings, booleans). Each new control is a single line of code. You can see the filter in the bottom textbox.

View attachment 108423

Here is the entire code (using the provided modules)

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String

  'Need final filter
  Dim StrFilter As String
  Dim AndOr As CombineFilterType

  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select

  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
  StrFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)

  GetFilter = StrFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
End Function

Building that functionality by scratch would be 20 times the code.

@Bean Machine if you post that db I can build the filter form. Make sure every control has a descriptive name like "cmboInstructor" not "combo1"

Hi! Attached is the DB w/ bogus data. Let me know if there is anything else you need. The query I am trying to use to filter data is called "qry_Srch".

 

[The_Doc_Man]

@Karer09334 - thank you for relaying an AI-generated response about nulls. Unfortunately, it says a lot without saying much that is relevant to the original question. Many of the points in this response are at best indirectly relevant to an Access environment. Some of them don't even apply to an ORACLE environment or a ShareBase (now defunct database) environment. We appreciate your desire to help but prefer a more directed, more personal response when possible.

 

[Isaac]

Allowing nulls in a list can provide certain benefits and flexibility in programming. Here are some reasons why allowing nulls in a list may be valuable:

1. Optional Values: Allowing nulls allows for optional values within the list. It means that certain elements can have a missing or unknown value, providing a way to indicate that information is not available or applicable.
2. Data Modeling: In some cases, nulls are necessary to accurately model real-world scenarios. For example, in a database, nulls can represent the absence of a value or an unknown state, allowing for more accurate data representation.
3. Compatibility: Allowing nulls in a list ensures compatibility with existing code and systems that might rely on nulls as a way of handling certain scenarios. It allows seamless integration with libraries, APIs, or databases that already use nulls for specific purposes.
4. Flexibility in Algorithms: Nulls can be useful when implementing algorithms that require differentiating between present and absent values. Algorithms like sorting, searching, or filtering can handle nulls based on specific logic or requirements.

However, it's important to consider potential drawbacks and challenges associated with nulls, such as null reference errors and increased complexity in handling nullable values. To mitigate these issues, programming languages and frameworks often provide mechanisms like null checks, optional types, or other constructs to help manage null values more safely.

When using nulls in a list or any data structure, it's essential to ensure proper handling, documentation, and clear communication within a development team to prevent unintended bugs or issues arising from null-related operations.

Ultimately, the decision to allow nulls in a list should consider the specific requirements of the system, the potential benefits, and the trade-offs associated with null handling in the context of the programming language and framework being used.

All that said, I still appreciate a database where the DBAs don't allow any nulls anywhere!

There are any number of default values that can take the place of the meaning of a null without having to fuss with it code wise

 

[The_Doc_Man]

Clever, but this is a good way to become marginalized very quickly. Not only are we reaching that point where an AI might pass the Turing test, but some people are approaching the point that they would fail one.

 

[ebs17]

There are any number of default values that can take the place of the meaning of a null without having to fuss with it code wise

Can that be elaborated on? Because I'm convinced of the opposite, but hopefully still able to learn.

NULL defines the unknown, undefined. For example, if I don't state my debts, i.e. use NULL, that is a completely different statement than if I state these debts with 0 as a substitute value.
This is not interchangeable.

 

[Gasman]

I apologize for the confusion, but as an AI language model, I don't have the capability to receive or process attachments. I can only provide text-based responses and answer questions based on the information you provide in our conversation. If you have any specific questions or need assistance with any topic, feel free to ask, and I'll do my best to help you.

Appears that is all they can offer?
Same with other thread they are in.

 

[Pat Hartman]

All that said, I still appreciate a database where the DBAs don't allow any nulls anywhere!

There are any number of default values that can take the place of the meaning of a null without having to fuss with it code wise

I do not admire "solutions" that manipulate reality. Null always means "unknown" and it is a uniform value and has been since relational databases originated in the 70's. Assuming the table is properly defined, I can rely on Null ALWAYS meaning "unknown" no matter what table I am looking at. If you choose to substitute an arbitrary value, you would never be able to use the same value in every instance, starting with the issue of different data types supporting different value ranges. Therefore, you would be required to have specific application DATA knowledge in order to be able to handle the concept of null in each specific field. Also, for a field that holds an actual number, as @ebs17 suggested, you have NO valid arbitrary value that can be used to represent unknown since 0 has a meaning.