Report filtered by form / combo boxes

[Tezdread]

Hi all,

I have a report working and it can be filtered based on what is selected within the combo boxes on a form. When nothing is selected from the combo boxes, all results are returned and when a selection is made from any of the combo boxes only those values return. Excelent!

The search form has four combo's:

Department
Survey Date
Term of Employment
Job Role

However, I've been asked to modify it and this is where I'm stuck.

I have to show what department, term of employment etc is selected at the top of each page. This again is no problem and I've implemented it so it shows whatever department is selected.

The problem I have is that if nothing is selected, 'All' should be displayed for Department or whatever como box has been left blank but it will only show the last department that was selected.

Can anyone help?

 

[databasedonr]

You could try an IIF in the field on your report, sort of like this (in the data source) on the text box:

=IIF(IsNull([YourcomboBox]), "All", "[YourComboBox]")

 

[Tezdread]

cheers data, what you've suggested works but only when nothing is selected in the combo box, whenever I select one of the list all I see in the report is the IIF statement?

 

[Tezdread]

found it, I had to remove the comments round the second form part

nice one data

 

[databasedonr]

Oops, right you are. Don't use the commas round the last field. Brain cramp. Sorry about that - and glad this helped.