Applying filter from form in query

[sanniep]

Hello,

I've got a form like this:

Name: _(free text input)__
Adress: _(free text input)_
City: _(free text input)_

When clicking a Apply Filter button on the form a query is started. The Query contains the 3 fields above with [forms]![Form1]![Name] /[Adress]/[City] filters. When entering data in the formfields and hitting the button the query shows the results as filtered in the formfields.

When I leave 1 of the formfields empty the query shows no result because it can't match the null-data. But this is just the thing I need..

When I don't enter a value in 1 of the formfields I want the query to don't filter that field at all. For example when I enter Name and City, let's say John and Amsterdam, I want the query to show the result of all Johns from Amsterdam and not leave the result blanc because the Adress is null..

Hope someone can help! Thanks, Sander

[PEB]

Hi Sander,

But in my autofilter forms, when i filter, when there is no specified value and it's blanc so there is not filter running on!

There is is Null not is null to specify if I want the empty or not empty fields...

When empty it's no filter activated on he field...

Maybe in your list has the space that is choosen???

Best regards

[sanniep]

Hi Sander,

But in my autofilter forms, when i filter, when there is no specified value and it's blanc so there is not filter running on!

There is is Null not is null to specify if I want the empty or not empty fields...

When empty it's no filter activated on he field...

Maybe in your list has the space that is choosen???

Best regards

Hi PEB,

Thanks for your help again!

When I leave the textbox empty and put a msgbox to the button with the value of the textbox as messageboxtext Access says "Invalid use of Null-data" so the value of the textbox when I leave it blanc is Null..

The question is how to let the query know that if I leave the textbox blanc it has to show everything instead of nothing..

I've tried a lot of iif's to the query where I say 'if textbox is blanc then value is not null". I've tried:

iif([forms]![form1]![txt_item] Is Null;Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item] ="";Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item] ="";<>"";[forms]![form1]![txt_item])
iif(IsNull([forms]![form1]![txt_item]);Is Not Null;[forms]![form1]![txt_item])
iif(IsNull([forms]![form1]![txt_item]);<>"";[forms]![form1]![txt_item])

All of these iif's return an empty query when ran with a blanc textbox..

I've also tried filling the textbox with default value "no filter" and using iif's:
iif([forms]![form1]![txt_item]="no filter";Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item]="no filter";<>"";[forms]![form1]![txt_item])

The Autofilter function you describe is just what I want. Maybe I should use VB, but how??

Hope you can help..

Sander

[MMcCarthy]

Try this

iif(Not IsNull([forms]![form1]![txt_item],[forms]![form1]![txt_item])

Hi PEB,

Thanks for your help again!

When I leave the textbox empty and put a msgbox to the button with the value of the textbox as messageboxtext Access says "Invalid use of Null-data" so the value of the textbox when I leave it blanc is Null..

The question is how to let the query know that if I leave the textbox blanc it has to show everything instead of nothing..

I've tried a lot of iif's to the query where I say 'if textbox is blanc then value is not null". I've tried:

iif([forms]![form1]![txt_item] Is Null;Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item] ="";Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item] ="";<>"";[forms]![form1]![txt_item])
iif(IsNull([forms]![form1]![txt_item]);Is Not Null;[forms]![form1]![txt_item])
iif(IsNull([forms]![form1]![txt_item]);<>"";[forms]![form1]![txt_item])

All of these iif's return an empty query when ran with a blanc textbox..

I've also tried filling the textbox with default value "no filter" and using iif's:
iif([forms]![form1]![txt_item]="no filter";Is Not Null;[forms]![form1]![txt_item])
iif([forms]![form1]![txt_item]="no filter";<>"";[forms]![form1]![txt_item])

The Autofilter function you describe is just what I want. Maybe I should use VB, but how??

Hope you can help..

Sander

[MMcCarthy]

By the way the IIf statement is separated by commas not semi colans.

[sanniep]

By the way the IIf statement is separated by commas not semi colans.

Hello mmccarthy,

Thanks for your advise. I'm applying the expressions from the Access Query designwindow, not from SQL or VB. In this rookie 'can't function wihout a GUI' interface I have to use semi colans, commas aren't accepted..

But I've tried your iif(Not IsNull([Forms]![Form1]![txt_item]);[Forms]![Form1]![txt_item]). After a run Access modifies the iif to:

iif(Not([Item])=IsNull([Forms]![Form1]![txt_item]);[Forms]![Form1]![txt_item])

You would expect that the criteria is only applied when the textbox is filled, when left blanc it doesn't apply any criteria but the bad news is that this expression also results in an empty query when I leave the textbox blanc.

Do you have other suggestions? Appreciate your help, thanks!

[Ragbrai]

Try this:

Like iif(IsNull([forms]![form1]![txt_item]),"*",[forms]![form1]![txt_item])

Good Luck