Filtering by Combobox and Date Range? (1 Viewer)

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
Why I can't see "PREVIEW content page". Its always error.
It is the same for me.


I appreciate SO very much all your suggestions but unfortunately nothing is working so far.

@arnelgp Maybe if I go about it another way, really all I need is to be able to select ALL records in the dropdown. The UNION select that I have seen you post about in other threads seems promising but I cannot make it work. Am I missing part of the equation?

I create the query and add the UNION line in the SQL, and when I run the query, I get <All> in the list. But when I go to my form, selecting <ALL> returns no records. I feel like I am once again missing something obvious.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
Thank you so much for your instructions
Unless you follow the instructions and post the string created by the code we're just guessing.
 

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
Unless you follow the instructions and post the string created by the code we're just guessing.
I am sorry because I am very much a beginner here but as I wrote earlier today, I followed your instructions and there was NO string to post here. It only said the word True.

It is quite possible that I did something wrong, but I have researched and tried and nothing else gave me any string at all. The only thing I did find was from another thread where someone said "you can't step into a procedure that takes arguments. You can set a breakpoint in the code and step from there." I tried setting breakpoints at various points but nothing has worked. The immediate window is just blank. Sometimes a macro window opens with no info and I can only select Close, and sometimes I get a Windows "ding" sound. Other than that, no string. I watched a video of how it is supposed to look when it is working and it definitely did not do that. Again, apologies, but I don't know what else to do with that.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
I followed your instructions and there was NO string to post here. It only said the word True.
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.
 

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
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!
 

Attachments

  • Screenshot 2021-03-23 075728.jpg
    Screenshot 2021-03-23 075728.jpg
    65.6 KB · Views: 114

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
42,981
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
 

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
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'
 

Attachments

  • Screenshot 2021-03-24 090107.jpg
    Screenshot 2021-03-24 090107.jpg
    79 KB · Views: 115

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
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

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

 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:41
Joined
May 21, 2018
Messages
8,463
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.

Code:
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.
 

Attachments

  • MultiFilterLight.accdb
    1.3 MB · Views: 263

Denise2020

Member
Local time
Today, 13:41
Joined
Mar 31, 2020
Messages
82
@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!
 

Users who are viewing this thread

Top Bottom