Filtering by Combobox and Date Range? (1 Viewer)

[Denise2020]

Greetings!

I have a form with a list of jobs that have dates and agencies attached. On the form, I have a dropdown in order to be able to show all jobs by a single agency. I also have two date range search boxes with a start date and an end date and a button to select all jobs within that date range. Some wonderful helpful people here helped me with this code several months back.

The problem is that it only will filter by date when I first select a single agency. There is no way to show a date range that includes all jobs If I am showing all jobs by all agencies and put in a date range, I get zero results. When I choose an agency and then the date range, I get the expected results. I thought that the If Not IsNull would cover the chance that no selection is made in the Agency (Uppdragsstallare) combobox.

How do I get results for a date range and all agencies? Code is as follows:

 

[theDBguy]

Hi. I don't see any code. Perhaps you might also consider posting a sample copy of your db with test data.

 

[Denise2020]

Many apologies but getting a browser error when trying to toggle it as code. I am not sure how to post it.

"Oops! We ran into some problems.
Oops! We ran into some problems. Please try again later. More error details may be in the browser console."



Edit: I don't know if this is allowed but I have put the code in a txt file because no matter what I do, I cannot post it here. So strange. Let me know if this is disallowed and I will delete it.

Something I noticed is that if I make a selection in the Agency (uppdragsstallare) combobox and then erase it by pressing backspace or delete, THEN I get all agencies by a selected date range. So it is works for a "chosen" empty uppdragsstallare, but not if one is not selected in the first place.

 

[Gasman]

Many apologies but getting a browser error when trying to toggle it as code. I am not sure how to post it.

"Oops! We ran into some problems.
Oops! We ran into some problems. Please try again later. More error details may be in the browser console."

You are not doing anything wrong. That appears to be a recurring error at the moment. I've not had it for a few days, and was hoping it had gone, but it appears not.

 

[Pat Hartman]

Put a stop in the code on the third If. print the contents of swhere to the immediate window. Look for syntax errors or post here and we'll look too

 

[Denise2020]

Thank you all.

TheDBguy, I was thinking of trying to upload a generic version of my db but it is in another language (Swedish) and it will take quite a lot of changes to make it usable to anyone here, I think. Or am I not considering something?

Gasman, I tried three different browsers thinking it was an issue with Chrome at first. Good to know it isn't just me, but sorry it is still a problem.

Pat, I actually don't get any errors at all. The code seems fine, no syntax errors, but it isn't bringing the results I hope for.

I mention this every time I post a question here, but everything I know about Access (which is little) is self-taught from Google and YouTube so unfortunately I am always very irritating and have a lot of questions. So I am quite possibly misunderstanding what the code is checking for here. I thought that If Not IsNull() would result in:

1. if there is a selection made in either the date range, agency, or both, it would check and compare those to bring back the filtered results
2. If there is NO selection, i.e. it IsNull, then it would just bring back all results, for both agency and date range.

Instead, it is bringing back zero results. So I can only think that a "no selection" in the combo or date boxes do not equal Null, does no selection = Empty? Is there a way to change or add to this so that it selects all records if nothing else is picked?

I have tried to write in something that tells it that if no selection is made, then Uppdragsstallare = "*" but nothing I try has worked.

Thank you very, very kindly for all of your patience and help. It is greatly appreciated!

 

[Pat Hartman]

Why don't all you dumb Republicans move to Texas and seceede from the Union as the Republic of Texas?

Without seeing the actual string created by the code we haven't a clue.

 

[Denise2020]

Without seeing the actual string created by the code we haven't a clue.

Ok, I am unsure what you mean by string created by the code. I opened the immediate window and ran the code but nothing showed up. I am sorry I don't know how to post/supply you with what you are asking for.

 

[Pat Hartman]

Put a stop on the line after the SQL string is build. Do that by clicking in the gray margin to the left of the code. Run the procedure. Access will stop at that line and open the VBA window. Print the contents of the string to the immediate window. Copy and paste it here where we can see it.

Use ? or Print to print the string. i.e.

? yourstringname
Print yourstringname

 

[Denise2020]

Pat, Thank you so much for your instructions, I am getting to it first thing this morning. In the below code, the string name would be 1=1? If so, the only thing I get in the immediate window is True. I am reading up further to see if I am again doing something wrong. Thanks again for your patience and help!

As a side note, interestingly, I narrowed down that the brower OOPS error is occurring when I try to use the double quote, quote, double quote combo in bold. Everything else will load fine. Not sure if anyone knows how or why that can be an issue but I thought I would just point out that that is what is causing the error in my case.


Private Sub btnValjDatum_Click()
Dim sWhere As String

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 & double quote quote double quote

If sWhere = "1=1" Then
Me.FilterOn = False
Else
Me.Filter = sWhere
Me.FilterOn = True
End If
End Sub

 

[smtazulislam]

Try to like that,

     sWhere = "1=1"
     If Not IsNull(Me!txtStartDate) Then
           sWhere = sWhere & " and int([Arendedatum] BETWEEN " & Format(Me!txtStartDate], "\#" & "mm/dd/yyyy" & "\#") & " AND " & Format(Me![TxtEndDate], "\#" & "mm/dd/yyyy" & "\#")

Edit:
sWhere = ""

 

[Denise2020]

Try to like that,

     sWhere = "1=1"
     If Not IsNull(Me!txtStartDate) Then
           sWhere = sWhere & " and int([Arendedatum] BETWEEN " & Format(Me!txtStartDate], "\#" & "mm/dd/yyyy" & "\#") & " AND " & Format(Me![TxtEndDate], "\#" & "mm/dd/yyyy" & "\#")

Edit:
sWhere = ""

I edited the sWhere to "" but am getting a syntax error with this, possibly this? But not sure how to fix it, getting expected end of statement with()

(Me!txtStartDate]

edit: sorted, removed ], testing gives me Syntax Error:

Syntax error (missing operator) in query expression ' and int([Arendedatum] BETWEEN # # AND # # and
[Uppdragsstallare] = "NBS"

 

[arnelgp]

maybe something like this.
(i am unable to post the code?)

 

[Denise2020]

maybe something like this.
(i am unable to post the code?)

Thank you! But this also gives me a Runtime error Missing Operator.

 

[Denise2020]

Could anyone kindly explain to me why the If Not IsNull() does not cover the contingency that no selection is made in the dropdown/combobox? It seems like this is the problem and why it gives no results. Does If Not IsNull() not mean Else ALL Records? If not, then what would refer to All Records? Sorry if my question is confusing.

 

[arnelgp]

oh, i missed the two #.

 

[Denise2020]

oh, i missed the two #.

Thank you once again. Unfortunately I get zero results when choosing dates but no Uppdragsstallare.

I have been messing with trying to add a Union Select in the query behind the combobox to add an <All> choice but while the query itself shows <All> in the list, selecting <All> brings zero records rather than all records as desired. Is this a track to follow or should I just skip this idea? Thanks again!

 

[arnelgp]

do you have Date picker on your 2 date textbox? you should to avoid confusion (when using different Date Region setting).

EDIT:
you can also make those (start/end date) textbox as combobox.
so you can limit only dates available on your table, eg:

combo0 (combobox)
Row Source Type: Table/Query
Row Source: Select [date field] from [your table] group by [date field]

 

[Denise2020]

do you have Date picker on your 2 date textbox? you should to avoid confusion (when using different Date Region setting).

EDIT:
you can also make those (start/end date) textbox as combobox.
so you can limit only dates available on your table, eg:

combo0 (combobox)
Row Source Type: Table/Query
Row Source: Select [date field] from [your table] group by [date field]

I do have a date picker on the two date textboxes, I think for our use, it would work much better than a combobox.

 

[smtazulislam]

I edited the sWhere to "" but am getting a syntax error with this, possibly this? But not sure how to fix it, getting expected end of statement with()

(Me!txtStartDate]

edit: sorted, removed ], testing gives me Syntax Error:

Syntax error (missing operator) in query expression ' and int([Arendedatum] BETWEEN # # AND # # and
[Uppdragsstallare] = "NBS"

Mr. @arnelgp sir give you nice code. I thing you follow his code.
And you can try this :
Note : If you have List box then used it.

Dim sWhere As String
     sWhere = " "
     If Not IsNull(Me![cboFilterUppdragsstallare]) Then sWhere = sWhere & " AND [Uppdragsstallare]=" & Me![cboFilterUppdragsstallare ]

     If Not IsNull(Me!txtStartDate) Then
           sWhere = sWhere & " and int([Arendedatum] BETWEEN " & Format(Me![txtStartDate], "\#" & "mm/dd/yyyy" & "\#") & " AND " & Format(Me![TxtEndDate], "\#" & "mm/dd/yyyy" & "\#")
        End If
    End If

    If sWhere <> "" Then
        sWhere = Right(sWhere, Len(sWhere) - 5)
    End If
    Me![YourSubform/ListBox].RowSource = "SELECT * FROM QueryName WHERE " & sWhere
    Me![YourSubform/ListBox].Requery

Another ways change last 6 lines .

If sWhere <> "" Then
        sWhere = Right(sWhere, Len(sWhere) - 5)
        Me.Filter = sWhere
        Me.FilterOn = True
     
    Else
        Me.FilterOn = False
    End If

Edit :
DELETE : "End If" Because double