Setting query criteria to be 'blank' depending on the criteria of a combo box

[whitty15]

I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).

However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.

Any help would gratefully be appreciated. Thanks

 

[whitty15]

IIF maybe???

Having thought about it slightly, am i correct in believing that I need to set the criteria for the 'actual completion date' to be along the lines of... If the combo box equals open, then display Is Null as the criteria, and if not then (i am unsure). and for the overdue part...would it be along the lines of...If the combo box equals overdue, then the criteria would also be is null And the criteria for required completion date < today ???

I have reason to believe that this may work, but am unsure of the correct way to input this to the query.

Grateful of any help

 

[EMP]

IIF maybe???

Try this criteria in a new column in the query grid, using the correct form name and combo box name:

- - - - - - - - - - - - - - - - -
Field: IIf([Forms]![FormName]![ComboBoxName]="Open", [Actual Completion Date] Is Null, IIf([Forms]![FormName]![ComboBoxName]="Overdue", [Actual Completion Date] Is Null And [Required Completion date]<Date(), True))

Show: uncheck

Criteria: True
- - - - - - - - - - - - - - - - -

Note: When the combo box is left blank, every record will be returned.

See Jon K's thread for explanations:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312


Alternatively, if there are many records, you can first build two separate queries and run whichever one is required based on user selection on the combo box. This can get rid of the need of using IIFs on a large table.

^

 

[whitty15]

Thanks....one last thing though

Many thanks in helping me with the IIf in the query, it works fine.

However, I wish to incorporate this into another query.
I wish to be able to make multiple selections from combo boxes on a form, and then when a button is pushed for the form to display the correct results, depending on what selections have (or may not) have been selected from the form.

I understand that the in the query i need to cover all possible selections, with regards to the criteria, that may be selected from the form.
The fields of which i wish to be able to narrow the search of a record by are, Actionee, Programme, Meeting and also the Status (Open or overdue) that I have just figured out.
How do i go about doing this? is it just a case of entering all the possible combinations into the criteria in the query?

 

[whitty15]

In addition to creating the multiple selection query....I expect that when the combo boxes are left blank all records are returned, and when more and more 'filter selections' are chosen, the results returned decreases.

However, if i do a search where part of the criteria is correct, but another part is incorrect for the record i am searching for, it still displays it. why is this?

 

[EMP]

...I expect that when the combo boxes are left blank all records are returned, and when more and more 'filter selections' are chosen, the results returned decreases.

Download Jon K's sample database from the link in my previous post. It does what you described.

^