Filter through combo box selection, how to select "All" 1

[BMKanun]

Hi guys,
When I make filtering of the records through combo box selection, everything is fine when there is a specific value of the combo related to the data in the table. When combo value is Null, the query selects nothing instead of everything (logical, isn't it?!). How to make this work, how to select "ALL" the results?
regards
BMK

 

[Andrzejek]

Could you have something like that in your combo:
[tt][blue]
Select:[/blue]
All
Susie
Adam
Brian
John
...
[/tt]

And when 'All' is selected (index of 0), go after all info in the table.

Have fun.

---- Andy

 

[PHV]

When combo value is Null, the query selects nothing instead of everything
SELECT ...
FROM ...
WHERE (yourField=[Forms]![yourForm]![yourCombo] OR [Forms]![yourForm]![yourCombo] Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

PHV, wouldn't that be:

SELECT ...
FROM ...
WHERE (yourField=[Forms]![yourForm]![yourCombo]
OR [blue]yourField Is Null[/blue])

?

Have fun.

---- Andy

 

[PHV]

Andy, no.

 

[Andrzejek]

Hmmm, interesting.

I am often wrong, but I try to learn.

If BMKanun has a table called People, and there is a field in there called FirstName, and in this field we have:
Susie
Adam
Brian
John

And [Forms]![yourForm]![yourCombo] is ‘Susie’ – you are saying the SQL should be:

SELECT People.*
FROM People
WHERE (FirstName = ‘Susie’ OR ‘Susie’ Is Null)

?

Have fun.

---- Andy

 

[PHV]

Andy, yes.
To select ALL records, set the combo to Null and then the query evaluates to:
SELECT People.*
FROM People
WHERE (FirstName = Null OR Null Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

OK, I see it now.

Nice twist

Have fun.

---- Andy

 

[BMKanun]

Hi again,
This doesn't work, or I'm doing something wrong.
When the All is selected (combo Value = null) means that criteria for the field is Null.
But I don't need Null criteria since no records has an empty field.
Instead of NULL as criteria, we need NO CRITERIA when we want to see all the records as result.

Maybe my syntax in the query is wrong, the query doesn't return anything

SELECT tblT1.ID, tblT1.F1, tblT1.F2
FROM tblT1
WHERE (((tblT1.F2)=[Forms]![tblT1 Query]![Combo10] Or (tblT1.F2) Is Null))
ORDER BY tblT1.ID;

bmk

 

[PHV]

SELECT ID, F1, F2
FROM tblT1
WHERE (F2=[Forms]![tblT1 Query]![Combo10] OR [Forms]![tblT1 Query]![Combo10] Is Null)
ORDER BY ID;


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BMKanun]

Thanks PHV!
You have made it!
The next logical step would by applying multiple filters!
Should I combine all the conditions now?

BMK