Need One Query to Search Multiple Date Fields, If Possible

  • Thread starter Thread starter Marcia
  • Start date Start date
M

Marcia

Hello. I know how to construct a query that will search between two
dates on *ONE* field... but I do not know how to construct a query
that will search on *THREE* fields.

For example:

In my Employee database, I have a HiringDate field. I also have three
other calculated fields that display the dates associated with the
employee's probationary period. For example, the employee must be
evaluated after three months from their hiring date, and again after
six months, and again after nine months. (These three evaluation
dates were calculated in my query using the DateAdd function.)

I need to create a parameter query (by entering a StartDate and an
EndDate) that will search on all three calculated date fields and
return a list of employees who need to be evaluated within that given
time period, regardless of whether it is their three-month, six-month,
or nine-month evaluation.

Is this possible? Any help would be greatly appreciated.

Thanks,
Jessi
 
Jessi,

Put your criteria in the design of the query like this...
Between [StartDate] And [EndDate]
.... in all three columns, but on different criteria rows of the query
design grid. So, if you see the SQL view of the query, the WHERE clause
will look something like this...
WHERE (([3MonthEval] Between [StartDate] And [EndDate]) Or
([6MonthEval] Between [StartDate] And [EndDate]) Or ([9MonthEval]
Between [StartDate] And [EndDate]))
 
Thanks for your help, Steve.

I tried your suggestion, and evidently I am still doing something
wrong. The query returns all records, regardless of the date. Here
is the way it looks:


SELECT tblEmployees.fldEmpLName, tblEmployees.fldHiringDate,
DateAdd("m",3,[fldHiringDate]) AS 3MonthEval,
DateAdd("m",6,[fldHiringDate]) AS 6MonthEval,
DateAdd("m",9,[fldHiringDate]) AS 9MonthEval
FROM tblEmployees
WHERE (((DateAdd("m",3,[fldHiringDate])) Between [StartDate] And
[EndDate])) OR (((DateAdd("m",6,[fldHiringDate])) Between [StartDate]
And [EndDate])) OR (((DateAdd("m",9,[fldHiringDate])) Between
[StartDate] And [EndDate]));

Do you see my error?

Thanks,
Jessi
 
Jessi,

Try putting the criteria in like this...
Between CDate([StartDate]) And CDate([EndDate])
 
Back
Top