Help With Checkbox and Query

[ianbar]

I might not have set this out in the best possible format but due to my limited vb knowledge I have a bunch of queries that are used as filters in macros that open the same form. I have a search form with a free text area for the search criteria, then two drop down boxes for 1 selecting the table to search and 2 select the field to search. This all works very well and I am happy with how it functions, although I apreciate it could have probably beeen done far quicker in SQL.

My problem is that I would like users to be able to select if they see records that are 'open' or all records within their criteria. I have a checkbox on the form that users tick to say they only want to see open records, if left blank then they see all records. The difficult part is a record is defined as open or closed by a date being presesnt in the closed date field. So I need to know how to use the tick box to say if a tick is present then the field should be Not Null and if a tick isn't present then show all records. Is this possible using a non SQL query?

Sorry this is so long!

 

[WhiteZiggy]

I have one way..In your query, you can add a field

IE.

Open : If date <> &quot;&quot; then &quot;Open&quot; else &quot;Close&quot;

Then you have a new field if your query and you can edit your form/query to place the correct option in the criteria...

Hope this helps..

Jeremy

 

[WhiteZiggy]

I just did something very similar..

I used the &quot;IIF&quot; function for the field in the query...

IE...

Status : IIF ( [HippyTable]![OutputFormat] = null, &quot;Closed&quot;,&quot;Open&quot

and then did the criteria to be what I needed...

SO yes, this way is possible...

Jeremy
WZ

 

[ianbar]

The IIF statement I tried was:

IIF([Forms]![frmsearch].[chkopen] = true, &quot;Is Not Null&quot;, &quot;&quot

What I am trying to get is if the tick box = true the criteria for the query is Not Null and if the tick box = False then I don't want a criteria.

Hope that makes sense.