Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    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?

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts