display rep based on 3 criteria from form, if blank show all + advice!

[frantik]

Hi

I am trying to print a report that is based on a query. To select the records I want to print I need to enter 3 criteria

project no
category
phase

When I input these criteria it allows me to preview the appropriate report, on pressing the preview button.

Question 1: at present I use combo boxes - on the query the report is based on I include references to those Combo boxes in the criteria section of the query builder - ie [Forms]![Fmcriteria]![Proj_no]

This works fine as long there is there a value selected in each box. If I leave one of the criteria blank it returns an empty report. What I require is that when one of these fields is left blank it shows me all all records in that field not none!

How do I do that?

And is this the best way of doing what I am doing - it seems fairly clumsy to me? Please any help??

Question2: If there are no records how do I display a message box to say try again?

Thanks

 

[Hillary]

Question 1: Use the concatenate function with * That way if you don't enter a value it will return all values. Example: Instead of =[Enter Project Number] you could use Like *&[Enter Project Number]&* Make sure to use the "Like" operator.

Question 2: You can write a macro and use the conditions. View>Conditions Under the first line of the macro...
Condition[Forms]![Fmcriteria]![Proj_no] like ""
Actionmsgbox
Under the second line of the macro...
Condition[Forms]![Fmcriteria]![Proj_no]<>&quot;&quot;
ActionOpenReport (set to print-preview mode)

Depending on how your database is set up you may want to add a refresh line to your macro at the beginning, just leave the condition like blank. Secondly, if &quot;&quot; doesn't work you could try Is Null.

Good Luck!

Hillary

 

[frantik]

Thanks Hillary - this works great!