how to set up this criteria

[AccessWater]

I used a Form to allow user select a group, then a report will be created for this group. I set up [Forms]![frmReportSelector]![cmb_ReportType] in report query. There are other variables such as project status(Not Started, Completed, etc) and completionDate.

Now I need to do one more thing, if "In Progress" group is selected, for the completed projects, I only want to show those finished within past two weeks. I have calcuated the DateDiff. But where and how to write this condition in query?


Thank you for your help.

 

[grnzbra]

You could use the OnUpdate event of the combo box to modify the WHERE clause of the query driving the report.

 

[AccessWater]

Hi, grnzbra, thank you for reply. I am trying to do as you suggested. However, how exactly to set up the SQL? there are dozens of variables from several tables in the query for the report. Besides the group, there are three other combo boxes in the form, together tehy determine the report content.
Only the group combo box has an additional condition.

Could you please explain more ? Thank you.

 

[grnzbra]

Check the help files for QueryDef. There is code in there that tells you how to modify the SQL of the query.

Before you do that, though, try using an IIf statement in the criteria

IIf(Forms!FormName!ComboboxName!Text = "In Progress", last two weeks date range criteria, other criteria)