Selection Criteria (AC97 SR-1)

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])"

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)

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