Add dynamic filter in query

[elbartje]

Hello all



I want to filter my query with the input of a form.
I use this filter: LIKE "*" & NameIputBox & "*" now when I leave the inputbox empty I don't see the blank cells, how can I fix this ?

[andy49]

Try using + rather than &


Sent from my iPhone using Tapatalk

[elbartje]

Does not work,

I tried to set the NameIputBox.value = "" so then I get this => LIKE "*" & "" & "*"
But still the NULL values get filtered out.

[orange]

Show us
a)the query sql
and
b)your tables/relationship view as jpg

[elbartje]

SQL:
SELECT ttLijnId, tbl8DRapport.memKlachtBetreft, tbl8DRapport.IDZone, tblZone.Zone, tblPersoon.strNaam, tbl8DRapportSoort.strSoortBeschrijving, tblProductlijn.strProductLijnBeschrijving, tbl8DRapport.dtm8DRapportAfgesloten, tbl8DRapport.strActualStatus
FROM tblPersoon INNER JOIN (tbl8DRapportSoort INNER JOIN (tblProductlijn INNER JOIN (tbl8DRapport LEFT JOIN tblZone ON tbl8DRapport.IDZone = tblZone.IDZone) ON tblProductlijn.lngIdProductLijn = tbl8DRapport.lng8DRapportProductLijnId) ON tbl8DRapportSoort.lngId8DRapportSoort = tbl8DRapport.lng8DRapportSoortId) ON tblPersoon.lngIdPersoon = tbl8DRapport.lngAanvragerId
WHERE (((tbl8DRapport.strReden) Like "*" & Forms!FilterForm!Woord_filter & "*") And ((tblZone.Zone) Like "*" & Forms!FilterForm!Zone_filter & "*"))
ORDER BY tbl8DRapport.dtmAanmaak DESC;

Forms!FilterForm!Woord_filter and Forms!FilterForm!Zone_filter are inputboxes from a form.
When I leave the inputbox is empty I also want to show empty cells.

This are the tables:

[andy49]

What are the default values of the textbox?


Sent from my iPhone using Tapatalk

[orange]

You could try to adjust this code (best guess at the moment)

WHERE (
        ((tbl8DRapport.strReden) LIKE "*" & Forms ! FilterForm ! Woord_filter & "*")
        AND ((tblZone.Zone) LIKE "*" & Forms ! FilterForm ! Zone_filter & "*")
        )

to

WHERE (
        ((tbl8DRapport.strReden) LIKE "'*" & Forms ! FilterForm ! Woord_filter & "*' ")
        AND ((tblZone.Zone) LIKE "'*" & Forms ! FilterForm ! Zone_filter & "*' ")
        )

[elbartje]

The default value is empty (null) , but if I use "" it also does not work.

@Orange: That does not work.

I got another idea but I dont know how to do that: If I create a new column depending on the culumn I want to filter: When It has no value I say it is 0 when it contains a value it is 1. Then I can use this new column to filter.

I can also just program it in VBA but I was hoping there is a more "clean way".

Already big thanks for the help.

[andy49]

In query design mode what about adding the or option of forms!filterform!woord_filter is null.


Sent from my iPhone using Tapatalk

[orange]

How about posting your database (zip format) with instructions to get to the specific issue?

[elbartje]

I fixed my problem, I just generate the correct SQL query with VBA depending on what I fill in in the filter.
It does not look clean but it works, if someone knows a cleaner or easier way



Private Sub btnZoek_Click()
On Error GoTo Err_btnZoek_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim SQLSlelect, SQLFrom, SQLOrder As String
Dim SQLWhere1, SQLWhere2, SQLWhere3 As String

Dim SQL As String
Dim Afgesloten_filter_bit As Integer


stDocName = "Formulier1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

SQLSlelect = "SELECT tbl8DRapport.lngId8DRapport, tbl8DRapport.str8DRapportNummer, tbl8DRapport.dtmAanmaak, tbl8DRapport.strReden, tbl8DRapport.lngAanvragerId, tbl8DRapport.lng8DRapportSoortId, tbl8DRapport.lng8DRapportProductLijnId, tbl8DRapport.memKlachtBetreft, tbl8DRapport.IDZone, tblZone.Zone, tblPersoon.strNaam, tbl8DRapportSoort.strSoortBeschrijving, tblProductlijn.strProductLijnBeschrijving, tbl8DRapport.dtm8DRapportAfgesloten, tbl8DRapport.strActualStatus "
SQLFrom = "FROM tblPersoon INNER JOIN (tbl8DRapportSoort INNER JOIN (tblProductlijn INNER JOIN (tbl8DRapport LEFT JOIN tblZone ON tbl8DRapport.IDZone = tblZone.IDZone) ON tblProductlijn.lngIdProductLijn = tbl8DRapport.lng8DRapportProductLijnId) ON tbl8DRapportSoort.lngId8DRapportSoort = tbl8DRapport.lng8DRapportSoortId) ON tblPersoon.lngIdPersoon = tbl8DRapport.lngAanvragerId "
SQLOrder = "ORDER BY tbl8DRapport.dtmAanmaak DESC;"


'Generate where statement:

If Nz(Forms.FilterForm.Woord_filter.Value, "") = "" And Nz(Forms.FilterForm.Zone_filter.Value, "") = "" Then
SQLWhere1 = ""
GoTo hier1
Else
End If

If Nz(Forms.FilterForm.Woord_filter.Value, "") <> "" And Nz(Forms.FilterForm.Zone_filter.Value, "") <> "" Then
SQLWhere1 = "WHERE (((tbl8DRapport.strReden) Like " & Chr(34) & "*" & Chr(34) & " & Forms!FilterForm!Woord_filter & " & Chr(34) & "*" & Chr(34) & ") AND ((tblZone.Zone) Like " & Chr(34) & "*" & Chr(34) & " & Forms!FilterForm!Zone_filter & " & Chr(34) & "*" & Chr(34) & ") "
SQLWhere2 = ")"
GoTo hier1
End If

If Forms.FilterForm.Afgesloten_filter.Value <> -1 Then SQLWhere2 = ")"

If Nz(Forms.FilterForm.Woord_filter.Value, "") = "" Then
Else
SQLWhere1 = "WHERE (((tbl8DRapport.strReden) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![FilterForm]![Woord_filter] & " & Chr(34) & "*" & Chr(34) & ") "

End If

If Nz(Forms.FilterForm.Zone_filter.Value, "") = "" Then
Else
SQLWhere1 = "WHERE (((tblZone.Zone) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![FilterForm]![Zone_filter] & " & Chr(34) & "*" & Chr(34) & ") "

End If

hier1:

If Forms.FilterForm.Afgesloten_filter.Value = -1 Then
If Nz(Forms.FilterForm.Zone_filter.Value, "") <> "" Or Nz(Forms.FilterForm.Woord_filter.Value, "") <> "" Then
SQLWhere2 = "AND ((tbl8DRapport.dtm8DRapportAfgesloten) Is Null)) "
Else
SQLWhere2 = "WHERE (((tbl8DRapport.dtm8DRapportAfgesloten) Is Null)) "
End If
Else

End If

SQL = SQLSlelect & SQLFrom & SQLWhere1 & SQLWhere2 & SQLOrder

Forms.Formulier1.RecordSource = SQL
Forms.Formulier1.Requery

Exit_btnZoek_Click:
Exit Sub

Err_btnZoek_Click:
MsgBox Err.Description
Resume Exit_btnZoek_Click

End Sub