Help with a Date Query

  • Thread starter fapa247 via AccessMonster.com
  • Start date
F

fapa247 via AccessMonster.com

Hi everyone

I've got a query that needs alittle guidence...can someone pleease help!

The aim of the query is to select employees whose start dates fall within a
predefined period. At present, the employees come from the employee table.
Each employee has a start date and some have end dates. Each employee is
issued certificates however the certificate they are given depends upon
whether their start date is within CERT_START_DATE and CERT_END_DATE. If the
employee has left, they can still be issued the certificate just as long as
their end date is After the cert_end_date.

I hope all this makes sense! The bit i need help with is the sql in the query.
So far, i've added -

<=[Employ_Start_Date] in the CERT_START_DATE but im not sure what to put in
the cert_end_date. I've tried <=[Employ_End_Date] but not all employee's have
an end date!

Can someone PLEASE help! all comments will be much appreciated

thanks!
 
C

Carl Rapson

What do you want to have happen if the [Employ_End_Date] is blank? Is the
employee still issued the certificate? If so, then try:

CERT_END_DATE >=
IIf(IsNull([Employ_End_Date]),[Employ_Start_Date],[Employ_End_Date])

If the employee is not issued the certificate when the end date is null,
try:

CERT_END_DATE >= IIf(IsNull([Employ_End_Date]),Date,[Employ_End_Date])

This assumes that CERT_END_DATE is earlier than the current date.

HTH,

Carl Rapson
 
Top