Subform not having the right query results in group recordsource

[Ruegen]

I have a subform with a group query as the record source



I've put a like operator on one of the grouped fields with wildcards so the user can type text on the main form and have it filter the subform when that person requeries the subform.

The field is AREA

it is grouped

IIf(IsNull([forms]![frmStatistics]![txtAreasfilter1]),[tblareas]![Area],([tblAreas].[Area]) Like '*" & [forms]![frmStatistics]![txtAreasfilter1] &   "*')

I don't know why it comes up with no results.

If I use like

Like '*QLD*'

then I get the correct results but when I use the iif I get nothing. If I don't put anything in the text box the true statement is ok and all results show.

[June7]

Why do you need the IIf()? Why not just:

Like "*" & [forms]![frmStatistics]![txtAreasfilter1] & "*"

[Ruegen]

Why do you need the IIf()? Why not just:

Like "*" & [forms]![frmStatistics]![txtAreasfilter1] & "*"

if text box is empty I want all the results to show, I am also filtering by date later but I want this to work first

[Ruegen]

I've uploaded an image.

I've used the WHERE group too with the same results and put the iif statement as a field to filter by 0 or 1 (sort of worked but won't help when I have null)

[June7]

That should show all results if textbox is empty if used in criteria row - not seeing that.

However, is it possible the field can be Null? Do you also want the records where field is Null?

[Ruegen]

oh sorry June, that works fine


I tried that before but I had no success and thought I had something wrong - I must have missed something. Apologies!