MS Access help

[blue60007]

OK, I'm running a query on a table, and I need to filter down by input on a form. Here's what I have on the Critieria field for the particular data field:

IIF(IsNull(F_BU()), " (???) ", F_BU())

F_BU() is the returned value (integer) from input... If the user inputs a number then the results are filtered down by that value just fine, BUT if the user leaves the input null, then I don't want to filter down by that field (ie, display all results that made it through the previous filters). If I just leave empty quotes (the " (???) " is what is in question), it filters down by results that have that field null (which there are none of)...I want to display ALL results if the returned value is null. I've tried wildcards and >0 but no luck. Any ideas? Let me know if I need to elaborate/more detail....

TIA

[faulkner132]

I'm assuming you are using this in a query statement, in which case it would be:

SELECT IsNull( [field name], [value to return if null... usually 0 or ''] ) AS FieldValue

If you are using the IIF statement in your code:

Variable = IIf( F_BU() Is Nothing, " ??? ", F_BU() )

[blue60007]

Thanks...I ended up doing it another way. When you clicked on the button to run this query, if it was null, I ran a seperate query that didn't filter by that field, and if it wasn't null...then I ran another query that did filter...I may be back with more questions...

[faulkner132]

Originally Posted by blue60007 Thanks...I ended up doing it another way. When you clicked on the button to run this query, if it was null, I ran a seperate query that didn't filter by that field, and if it wasn't null...then I ran another query that did filter...I may be back with more questions...

That works, but it may be best to consolidate to a single query. This way if your DB changes or you need to modify the query, you only have to change it in one place.