CODEWITHSUNDEEP
ms-access
Filipa
Filipa

Reputation: 35

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!

Upvotes: 1

Views: 92

Answers (1)

Gustav
Gustav

Reputation: 55831

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.

Upvotes: 2

Related Questions