Report filters using combo boxes & null value question

[Neobeowulf]

Team,

I have a report that is filtered by a selection in a combo box. What I need to do at this point is add another filter in another combo box to filter the report. The user should be able to use on or the other or both.

Currently in the query, I have [Forms]![Main][CmbStatFilter] in the Status column and [Forms]![Main][CmbUnitFilter] in the Unit column.

I can select both filters and it works find if there are results, but if one combo box or the other is empty(null) the report doesn't generate anything at all.

So, my question is, how do I get only one of my filters to work without having to put something in the other? I'm just guessing that I need to do something with null, but I don't know what.

 

[pbaldy]

Does this work for you?

http://access.mvps.org/access/queries/qry0001.htm

 

[Neobeowulf]

Sure doesn't.

When I go to my query & put in [Forms]![Main].[Cmbfltstatus] OR [Forms]![Main].[Cmbfltstatus] Is Null, when I close & save the query & reopen it, it moves the expression & the Is null to a new column.

I also tried putting the second part of the expression in the "or" block that's below criteria, that didn't work either.

The reports still show up blank when one or the other of the combo boxes is left blank.

 

[pr2-eugin]

Try.. This..

SELECT [COLOR=Blue]something[/COLOR] 
FROM [COLOR=Blue]somewhere[/COLOR] 
WHERE [COLOR=Blue]soField[/COLOR]=[Forms]![Main].[Cmbfltstatus] OR [COLOR=Blue]something[/COLOR] [COLOR=Red][B]Like Nz([/B][/COLOR][Forms]![Main].[Cmbfltstatus][B][COLOR=Red],"*")[/COLOR][/B]

 

[Neobeowulf]

Okay I tried it again the first way you told me to. When I closed & reopened, the query shuffled itself around...and it works...somehow.

I'm not somewhere that I can upload a pic to a sharing site & show you exactly what i'm talking about.

 

[Neobeowulf]

Okay I spoke too soon. It works 3/4 of the time. The filter works when both combo boxes have a value, it works when The status has a value but not the unit, but it still does not work when the unit has a value but not the status.

when I put [Forms]![Main].[Cmbfltunit] OR [Forms]![Main].[Cmbfltunit] Is Null in the criteria for unit--

--and [Forms]![Main].[Cmbfltstatus] OR [Forms]![Main].[Cmbfltstatus] Is Null in the criteria for the status, once I save & close the query and reopen it, access automatically rearranges the expressions I put in. See attachments. Access fills in lots of blocks I didn't fill in and puts the expression in the last two columns.

Again...it works 3/4 of the way, but I can't figure out why it won't filter just by the unit.

 

[pr2-eugin]

Okay..I am missing something here.. could you please explain exactly what you are doing?

 

[Neobeowulf]

Okay..I am missing something here.. could you please explain exactly what you are doing?

I was missing something too. I have a test database that's I use before I try it on the live database. When I tried it on my test database everything worked 100%. When I tried it on the live database, it only partially worked. There wasn't anything wrong with the expression Pbaldy posted, it worked great.

What was happening:

On my test database, the query I use for the report feeds off the main table. Pbaldy's suggestion worked 100% in this instance.

On the live database, the query used in that database was based off another query (this is the piece I was missing). So the expression was only working 3/4 of the way.

The fix: I redid the query that was based on another query to be based off the main table like my test database. Problem solved.

I hope that made sense. When you guys give me solutions it's like you're speaking greek. When I give you my problems I know i'm speaking chinese