How to Update a Field using DateAdd and IIF in a form

[Awesome!]

Hi All,I'm new to ACCESS and to this forum. Please let me know if this thread should go in a different place or any other rules of etiquette.

I have a field in a form which indicates which date the person was last contacted [LastContact_Date] and I have a second field which states if the person is either ACTIVE or INACTIVE [ActiveInactive]. You would become inactive if the last time you were contacted was greater that 6 months ago.

I built an update query which works. It has the following IIf statement

IIf(DateAdd("m",6,[tblMAIN]![LastContact_Date])>Now(),"ACTIVE","INACTIVE")

It would be good if the [ActiveInactive] field would update when you tab past the [LastContact_Date] field but not essential.

Which is the best way to update a form field based on another form field? Do I use this update query in the [LastContact_Date] field or the [ActiveInactive] field in the form or table? Is there a better way than an update query to do the same thing?I am also new to VBA but I can copy and paste well enough if needed.

Thanks in advance,



Awesome!

[June7]

Saving calculated data (data dependent on other data) is often a bad idea. The Active/Inactive value can be calculated when needed.

[Awesome!]

Thanks June7. Good advice. You are right that I don't need this calculated data saved.

But how do I get this data to be visible in the form even if it not saved?

Thanks,

Awesome!

[June7]

Options:

1. create field with the expression in a query and use query as form RecordSource and bind textbox to the constructed field

2. put expression in ControlSource property of textbox

[Awesome!]

Thanks,

I tried the second option and I am getting a #name? error.

...but your statement about putting calculated fields in an form is a solid piece of advice.

I think I will change my strategy and put a this calculated field in a report that only outputs the inactive people. I think this is more of what I am looking for.

So I will make an attempt at creating this report and post to a new thread in the reports section if I have any questions.

Any links to a tutorial about adding expressions to a report would be appreciated.

Thanks!