Filtering by Combobox and Date Range?

[Denise2020]

There would always be a value. Please make sure you have stopped the code on the correct line. The code actually needs to run up to that point in order for Access to build the string. This is a basic part of debugging so it is worthwhile trying to do this so you understand how it works.

I absolutely want to learn this and get this right, you are so right. I have tried putting the stop on every line in the code just to test it out and literally nothing comes up when I run it. Tell me if I am totally doing this wrong.

I put a stop in the code by clicking the grey border which puts a red dot. I have tried clicking Compile Database, Continue/F5, and also by clicking the button in my form. No joy. No string whatsoever in the Immediate Window. Here is an example of what it looks like.

Testing out other parts of your directions I get the following/second screenshot. Please bear in mind I feel like a complete moron at this point because I certainly seem to be missing something in your instructions but it just doesn't seem to be giving me the results you are looking for. Sorry again and thank you so much for any and all help!

 

[Denise2020]

The stop doesn't do anything except stop the code. You have to open the form and do whatever you do to run the code. When it gets to the line with the stop, the code stops and the vba window opens. YOU have to not put a command in the immediate window to print out the string.

? sWhere followed by enter. Then copy what gets printed

Thank you so much! Here is the screenshot and the string that was printed when I selected dates but no Agency:

?sWhere
1=1 and [Arendedatum] between #8/1/2020# and #3/24/2021#


Here is the second attempt where I selected Agency and Dates:

?sWhere
1=1 and [Arendedatum] between #8/1/2020# and #3/24/2021# and [Uppdragsstallare]='NBS'

 

[Denise2020]

I have been toying with this concept for days but is there a way to add something like:


sWhere = "1=1"
If Not IsNull(txtStartDate) Then sWhere = sWhere & " and [Arendedatum] between #" & txtStartDate & "# and #" & txtEndDate & "#"
If Not IsNull(cboFilterUppdragsstallare) Then sWhere = sWhere & " and [Uppdragsstallare]='" & cboFilterUppdragsstallare & "'"
If IsNull(cboFilterUppdragsstallare) Then sWhere = sWhere & " and [Uppdragsstallare]= " & """" 'where this somehow points to ALL records?


Of course this doesn't work at all, I am unsure how to get where I am trying to go.

Immediate window results:
? sWhere
1=1 and [Arendedatum] between #1/1/2021# and #3/24/2021# and [Uppdragsstallare]= "

I thought that if [Uppdragsstallare]=* or "" or "*" that it would somehow include all records but I guess I am thinking about queries...

 

[theDBguy]

Hi. Sorry, I lost track of this discussion. I don't know if this will help at all, but maybe take a look at the file I uploaded in this other post to see if you can use any of it. Good luck!

MS Access combo box filter subform with Select Distinct

Hello, I have the following: - tblProductList - table with prod1, prod2 and prod3 fields - frmProductList - form, made automatically when I made the subform - frmProductSearch - main form - sfProductList - subform, takes values from the table - cBx1, cBx2 and cBx3 - combo boxes that act as...

www.access-programmers.co.uk

 

[MajP]

I have made some modules that make it realeasy to get filter from controls. No need to worry about delimeters, sql strings, and basically requires little code. This will handle the issue of selecting 1 or many controls.
Basically for each control the code is
someVariable = getFilterFromControl(TheControlName)

Works with two between textboxes, comboboxes, listboxes, multiselect listboxes. Dates, String, and numerics.
Then there is a single function to combine all your strings.

Don't focus on the modules just on how they are used. At least look at the CSQL for you delimiters.

For example the below is the entire code to filter the demo form with 7 controls. 1 Multiselect, 4 combos, 1 text box, one date between filter. Dates, Strings, and numerics. Using the two included modules I can build a form with many controls in just a few minutes.

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

This should demo how to use the modules.

 

[Denise2020]

@theDBguy and @MajP Thank you both so much! Those are both very interesting and actually solves another issue that I had with another form so I greatly appreciate it! I am going to try to implement that asap. Like the newbie you mentioned, I also was trying to get the functionality of the filters into my forms. I created several of my forms as split forms but I missed that and had implemented very clumsy cboboxes that could only filter one thing at a time. This is excellent!

But for this particular issue, I am playing with it a bit and wondering how I could implement adding a date range search into that as one of the options on the Search Form? My boss needs to be able to create reports that are either filtered by Agency or not, and by date ranges, which vary depending on the report, so they could be monthly, weekly, quarterly, or yearly, for example. It just depends. So the need is to be able to filter by date range on all records or by date range and also filtering by Agency.

Thank you again so much!