IIf statement problems : i) refresh and ii ) #Type!

[alan2013]

Hi.

I wonder whether someone might be able to offer me some help with the following, please.

I'm trying the following on an unbound text field, named 'Stage', on a form :

=IIf(Date()<[StartDate],"A",IIf(Eval(Date() Between [StartDate] And [EndDate]),"B",IIf([DueDate] is not null and Eval(Date() Between ([EndDate]) And [DueDate]),"C1",IIf([DueDate] is null and Eval(Date() >[EndDate])),"C2",IIf(Date()>=[DueDate],"D","")))))

I'm encountering a couple of problems with this :

i) when I add/change dates in any record, the Stage field doesn't refresh instantly. I have to force it to refresh.

ii) when StartDate, EndDate and DueDate are all blank, I get #Type! in the field. How may I remove the #Type! for records where there aren't a set of dates for the IIf to work on ?

Thank you for any help you can offer.
Al

 

[John Big Booty]

You could use the Lost Focus events of the fields involved to for a Requery.

Me.Requery

Should do the trick.

To over come the #Type error you could use the Nz() function to provide a working value when one of your controls hold a Null value