Results 1 to 2 of 2
Thread: Query criteria (2000)
2006-01-05, 14:25 #1
- Join Date
- May 2003
- Manchester, Gtr Manchester, England
- Thanked 0 Times in 0 Posts
Query criteria (2000)
Hello I have a query where the criteria on two fields is taken from a user input form. If use selects no inouts then I want the query to run all data. Specifically I have a date field and one for consultants, so if the date range is selected but no consultant then all consultant records for that date range are shown, but if a consultant is selected and no date range I would like to see records for that consultant for all dates.
Currently in the citeria row for consultant I have IIf([forms]![frmreports]![cc] Is Not Null,[forms]![frmreports]![cc],[tblindividuals].[individual ID]), in the date I have is null or is not null with two more fields of [Forms]![frmReports]![cmbend] with criteria of >=[tblmain]![date] Or Is Null and [Forms]![frmReports]![cmbstart] with criteria of <=[tblmain]![date] Or Is Null (these last three are set to where)
When I select a cc and no date range, this works fine but where I put a date range in and no cc, all records for all ccs for all dates show! What would I need to modify?
2006-01-05, 14:32 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Query criteria (2000)
You can remove the "Is Null Or Is Not Null" condition - it is ALWAYS true.
Also remove the criteria for the consultant. Instead, enter [Forms]![frmReports]![cc] in a new column, set the Total option to Where and enter the following in the criteria row:
[tblIndividuals].[Individual ID] Or Is Null
I assume that Individual ID is the consultant field.