Search code examples
ms-access

Autofill a field based in a Data Field in a Table

Hope you're doing well. I have a question in Access. I have an Employee Table with some Fields and 2 of them are 'EndofContract' and the other is 'Condition'.

The EndofContract field is in date format (is the date in which the employee stopped working in the company) and what I want do to is autofill the Condition field with Inactive or Active, based on the date of EndofContract. Basically:
if EndOfContract is blank I want to show Active in Condition;
if EndOfContract has a date that is previous to today's date, I want to show Inactive in Condition

I've tried various different things and the last one was going to Design View » Condition Field » Lookup » RowSource and in the query designer I wrote this condition IIf(Date()>[EndofContract];"Inactive";"Active") but it doesn't work. Already tried an AfterUpdate macro but I can't understand how to change the value of every record.

Hope somebody can help me and thank you!

Solution

  • Have a textbox on your form where you display the data.

    Assign this expression as its ControlSource:

    =IIf(Nz([EndofContract],Date())>=Date(),"Active","Inactive")

    or, if semicolon is your list separator:

    =IIf(Nz([EndofContract];Date())>=Date();"Active";"Inactive")

    Or, remove the field Condition from the table and use a query:

    Select 
   *, IIf(Nz([EndofContract],Date())>=Date(),"Active","Inactive") As Condition
From 
   EmployeeTable

    If EndofContract is text, not a true DateTime value, try this:

    Select 
   *, IIf(Nz(CVDate([EndofContract]),Date())>=Date(),"Active","Inactive") As Condition
From 
   EmployeeTable

    This requires no updates.