ADP Status based on Dates

  • Thread starter Thread starter JohnR
  • Start date Start date
J

JohnR

I have three date fields, EffectiveDate, DisconnectDate, and SuspendDate. I
want to create a status field where the result will be Active, Inactive, or
Suspended based on whether there is a date in the field. What is the SQL
Statement needed to accomplish this in an Access Project?

Status EffectiveDate DisconnectDate
SuspendDate
Active Has Date Is Null
Is Null
Inactive Has Date Has Date
May or May not have date
Suspended Has Date Is Null
Has Date

Thank you in advance for your assistance.

John
 
Hi John,

I usually create a user-defined function to solve multiple criteria. If
I am in your shoe, I will create a short function that accepts those
three parameters and the return value is either string or numeric. The
drawback is the speed, and it can only be used with MDB.
But it is easier to maintain, esp. if there will be any changes on
those criteria in the future.

HTH

Anton
 
Back
Top