Status Indication (1 Viewer)

[Jonny45wakey]

Hi All

Struggling to correctly structure the VBA to do the following, any help is much appreciated. :-

I have a textbox called "status" on a form which has conditional formatting applied and dependent on values in other textboxes the status textbox value should change and subsequently change colour too!

Other textboxes on the form which support this operation are:-

txtMonth_Raised (obtains the mm from txtDate_Raised)
txtCurrent_Month (obtains the mm from =Now())
txtForecast_Completion_Date (entered on form when applicable)
txtDate_Closed (entered on form when applicable)
txtDate_Raised (entered on form when applicable)
txtForecast_month (obtains mm from txtForecast_Completion_Date)

The logic should work as follows:-

1. If forecast_completion_date textbox is empty then me.status.value = "New" - status box turns blue

2. If forecast_completion_date textbox isn't empty then me.status.value = "Ongoing" - status box turns amber

3. If Date_closed textbox isn't empty then me.status.value = "Completed" - status box turns green

4. If Date_closed textbox is empty and txtForecast_month is less than txtCurrent_Month then me.status.value = "Overdue" - status box turns red

Hope this makes sense and someone better at VBA than me can structure this code for me.


Thanks in advance

Jonny

 

[theDBguy]

Hi Jonny. Just a quick shot at it.

=IIf(IsDate([Date_closed]),"Completed",IIf(IsNull([Date_closed]) And Month([txtDate_Raised])<Month(Date()),"Overdue",IIf(IsDate([forecast_completion_date]),"Ongoing","New")))

(untested)
Hope it helps...

 

[Jonny45wakey]

Thanks theDBguy, where does this code go as i intended it for the form afterupdate event in the ms access form and it doesn't like that unfortunately ?

Maybe i wasn't clear enough, apologies.

Thanks

Jonny

 

[theDBguy]

Thanks theDBguy, where does this code go as i intended it for the form afterupdate event in the ms access form and it doesn't like that unfortunately ?

Maybe i wasn't clear enough, apologies.

Thanks

Jonny

Hi Jonny,

For now, try it as the Control Source of an unbound Textbox, just to see if the result is correct first.