Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Selection Criteria (AC97 SR-1)

    Have searched this forum for a answer, got close, but no solution.

    Data has a key of Date; Shift (Day, Afternoon, Night)

    I wish to display data on a form, which will allow a Start Date, End Date, or Shift.

    I can achieve this with Text Boxes (Start & End Dates) & a Combo box (For Shift) in header of form. I then have a filter applied, in the after_update event of all three controls. The filter contains two fields, one for date, and one for shift, with appropriate criteria referencing the fields in the header of my form. The date parameter has a 'BETWEEN' clause.

    PROBLEM: I wish to expand the selection in the above controls, to include an 'ALL' option.

    i.e. All Shifts; Start date from inception, or End Date to current date.

    I have been trying to use 'Iif' and 'LIKE' to select '*', but alas I get an error message implying that the expression is to complex to evaluate ????"

    Below is the current critera from my filter query. Any help would be appreciated.

    For the Dates Field: ">=[forms]![frmReviewReclaimData]![SelectStart] And <=[forms]![frmReviewReclaimData]![SelectEnd]"

    For the Shift Field: "Like IIf(IsNull([forms]![frmReviewReclaimData]![cboSelectShift]),"*",[forms]![frmReviewReclaimData]![cboSelectShift])"

    Thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selection Criteria (AC97 SR-1)

    Hi Devious,

    Here is a query that will return all values for one of the controls if it is left empty.

    The query looks complicated in the design grid, so I'll post the SQL. You must substitute the appropriate table name for tblDevious, and the appropriate field names if they are different from the names used below.

    SELECT *
    FROM tblDevious
    WHERE (tblDevious.Date>=Forms!frmReviewReclaimData!Selec tStart Or Forms!frmReviewReclaimData!SelectStart Is Null) And (tblDevious.Date<=Forms!frmReviewReclaimData!Selec tEnd Or Forms!frmReviewReclaimData!SelectEnd Is Null) And (tblDevious.Shift=Forms!frmReviewReclaimData!cboSe lectShift Or Forms!frmReviewReclaimData!cboSelectShift Is Null);

    The WHERE condition has three parts, one for each of the controls:

    For SelectStart:
    (tblDevious.Date>=Forms!frmReviewReclaimData!Selec tStart Or Forms!frmReviewReclaimData!SelectStart Is Null)

    For SelectEnd:
    (tblDevious.Date<=Forms!frmReviewReclaimData!Selec tEnd Or Forms!frmReviewReclaimData!SelectEnd Is Null)

    For cboSelectShift:
    (tblDevious.Shift=Forms!frmReviewReclaimData!cboSe lectShift Or Forms!frmReviewReclaimData!cboSelectShift Is Null)

    As you see, the three parts have the same structure.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selection Criteria (AC97 SR-1)

    Many, Many Many thanks HansV. Your solution worked Purrfectly first time. SQL (for those interested) is now:

    SELECT *
    FROM tblScrapReclaim
    WHERE (tblScrapReclaim.Date>=forms!frmReviewReclaimData! SelectStart or forms!frmReviewReclaimData!SelectStart is null) And (tblScrapReclaim.Date<=forms!frmReviewReclaimData! SelectEnd or forms!frmReviewReclaimData!SelectEnd is null) AND (tblScrapReclaim.Shift =forms!frmReviewReclaimData!cboSelectShift or forms!frmReviewReclaimData!cboSelectShift is null);

    I can now select a value or leave Null and form selects and Totals correct data

Posting Permissions

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