Solved Return all records in form if combo box is null (1 Viewer)

[Uldis007]

Greetings to all!

I am trying to create a filter (for a form based on a query) using the combobox [StatusSearchBox], but it only works when a parameter is selected, if the combobox is empty the form remains empty. I've tried several options
1) Like "*" & [StatusSearchBox] & "*"
2) [StatusSearchBox] or [StatusSearchBox] is null
3) IIf(IsNull([StatusSearchBox]),True,[StatusSearchBox]=[StatusSearchBox]
but they don't work.
Please help!
Here is the sql of my query

SELECT RemontaPieteikumi.RemontaPieteikumaNumurs, RemontaPieteikumi.PieteikumaDatums, RemontaPieteikumi.ReģistrācijasNumurs, RemontaPieteikumi.GarāžasNumurs, RemontaPieteikumi.Marka, RemontaPieteikumi.Modelis, RemontaPieteikumi.PieteikumaID, LastPieteikumaStatusQ.LastOfStatuss, Darbinieki.SistēmasLietotājs, [Statusi - ServisiQ_Crosstab].[Gaida tāmēšanu], [Statusi - ServisiQ_Crosstab].Tāmēšanā, [Statusi - ServisiQ_Crosstab].[Gaida servisu], [Statusi - ServisiQ_Crosstab].Servisā
FROM Darbinieki INNER JOIN (((RemontaPieteikumi LEFT JOIN PieteikumaStatus ON RemontaPieteikumi.PieteikumaID = PieteikumaStatus.PieteikumaID) LEFT JOIN LastPieteikumaStatusQ ON RemontaPieteikumi.PieteikumaID = LastPieteikumaStatusQ.PieteikumaID) LEFT JOIN [Statusi - ServisiQ_Crosstab] ON RemontaPieteikumi.PieteikumaID = [Statusi - ServisiQ_Crosstab].PieteikumaID) ON Darbinieki.DarbiniekaID = RemontaPieteikumi.DarbiniekaID
GROUP BY RemontaPieteikumi.RemontaPieteikumaNumurs, RemontaPieteikumi.PieteikumaDatums, RemontaPieteikumi.ReģistrācijasNumurs, RemontaPieteikumi.GarāžasNumurs, RemontaPieteikumi.Marka, RemontaPieteikumi.Modelis, RemontaPieteikumi.PieteikumaID, LastPieteikumaStatusQ.LastOfStatuss, Darbinieki.SistēmasLietotājs, [Statusi - ServisiQ_Crosstab].[Gaida tāmēšanu], [Statusi - ServisiQ_Crosstab].Tāmēšanā, [Statusi - ServisiQ_Crosstab].[Gaida servisu], [Statusi - ServisiQ_Crosstab].Servisā
HAVING (((LastPieteikumaStatusQ.LastOfStatuss)=IIf(IsNull([StatusSearchBox]),True,[StatusSearchBox]=[StatusSearchBox])))
ORDER BY RemontaPieteikumi.RemontaPieteikumaNumurs DESC;

 

[Ranman256]

Don’t put the null in the query ,filter in the form.
the form shows all records, then filter:

if isNull(searchBox) then
    Me.filterOn = false
else
    Me.filter = “[field]=‘“ &  me.searchBox & “‘“
    Me.filterOn = true
end if

 

[CJ_London]

have you tried

HAVING LastPieteikumaStatusQ.LastOfStatuss=nz([StatusSearchBox], LastPieteikumaStatusQ.LastOfStatuss)

you might also try using the WHERE clause rather than HAVING

(WHERE clause appears before GROUP BY)

and can you confirm that the StatusSearchBox is actually null and not a zls?

 

[arnelgp]

also:
... HAVING LastPieteikumaStatusQ.LastOfStatuss = IIf(IsNull([StatusSearchBox]), LastPieteikumaStatusQ.LastOfStatuss, [StatusSearchBox])

 

[Uldis007]

Thank you all for your response, I really appreciate it.
@ Ranman256 solution was exactly what was needed.
Thanks again and have a nice day