suborm field issues

[bigmac]

hi all can you help please, I have a subform that contains several fields , in particular are two fields [six month update] ( date field) and [status] (text field), my problem that I cannot solve is this when the subform is updated then I want the status value to change according to the date of six month update, example if date in six month is <date() then the value in status = "expired", this I need to automatically work every time the subfom updates, and for every record in the subform.
this is driving me mad , cheers

 

[burrina]

Try this:

=DateSerial(Year(Date()),Month(Date())-6,Day(Date()))

Then set your Status field to Expired.
HTH

 

[bigmac]

burina you misunderstand me I think, I can look at the date in the six month update field by using <=date() , this tells me if the date is past todays date , I need to know is how to set the status field to "expired" if this condition is met , and set this up so every time the subform is updated that this will run on all recordsets shown. any ideas please

 

[Galaxiom]

Status ControlSource property:

=Iif([Six Month Update]<Date(),"Expired","Current")

 

[bigmac]

Galaxiom does the "current" mean that if the condition is not met then the status value will stay the same as before?

 

[jdraw]

Mac,

Syntax and example at http://www.techonthenet.com/access/functions/advanced/iif.php

 

[bigmac]

Galaxiom, I have tried putting this in the subform on update properties but still no joy when the subform updates with new records nothing changes , am I missing something here ?

 

[bigmac]

Status ControlSource property:

=Iif([Six Month Update]<Date(),"Expired","Current")

I have put this in the status control source properties and it works , but it does not change the values in the subform table to the same as the sub form, ( record 1234 status in the subform is now showing "active" , but in the subform table record 1234 still shows expired) do you know how to update the table with the new values please?

 

[Galaxiom]

I have put this in the status control source properties and it works , but it does not change the values in the subform table to the same as the sub form,

It won't because the textbox is bound to the expression instead of a field in the subform's recordset.

This is correctly normalized operation. The Status should be calculated from the SixMonthUpdate each and every time you need to know it.

If you store it, the Status field will only be correct when the form is opened. The next day it could be wrong. Any records that are not loaded to the form could also be wrong.