Filter issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

Here is my situation. I have a query that needs to return data depending on
certain criteria. There are 3 date fields that the query has to look at:
BOY, EOY and MOY.
If all 3 fields are blank, I don't want the query to display those records.
If any one of the fields has a date between 7/1/2006 and 6/30/2007, then I
want the query to return that record.

There is one more thing that has to be taken into consideration. There are
2 other fields that must be looked at and can be done separately. If the
difference between the enrollment date and termination date fields is less
than 45 days, I don't want those records returned either.

Can anyone help me with this?

Thank you in advance for your expertise and knowledge.
 
I'm assuming that you are doing this in the query design grid.

For the BOY, EOY and MOY fields, enter
Between #7/1/2006# and #6/30/2007#
in the criteria field, in a SEPARATE ROW for each field.

For the criteria for the 2 other fields which must be looked at, enter the
criteria in EACH of the rows where the BOY, EOY and MOY criteria are
entered.

For the difference between enrollment date and termination date, add a
calculated field to your query:
ElapsedDays: DateDiff("d",[NameOf
EnrollmentDateField],[NameOfTerminationDateField])
(substituting the correct field names for enrollment date and termination
date fields), and set the criteria for this field (again in each criteria
row) to >=45

HTH,

Rob
 
Back
Top