Changing a countrol source based on a field (1 Viewer)

[NickDenyer]

Hi all!

Recently got some help with the following code (thank you arnelgp!):

=IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval","30 days target has elapsed")

This allowed me to develop a reliable countdown timer based on a date set in Field1. Users enter a date and have 30 days to complete the whole form. However, I want this 30 day timer to stop once data (date) has been entered into another field (Field2), displaying a message such as "30 day target achieved".

Any ideas on manipulating the expression above?

Thank you!

 

[arnelgp]

just add it to our expression:

=IIf(Nz([Field2], 0) > 0, "30 day target achieved", IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval","30 days target has elapsed"))

 

[NickDenyer]

just add it to our expression:

=IIf(Nz([Field2], 0) > 0, "30 day target achieved", IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval","30 days target has elapsed"))

Thank you again arnelgp! I notice it works great, except that in my Field2 I tested a few months in the future, and it still says 30 day target achieved...any thoughts? Does it require some form of Date() expression?

 

[arnelgp]

so we have an advanced date (i think this is the target date).

=IIf(Nz([Field2], 0) <> 0 And Nz([Field2], 0) <= Date(), "30 day target achieved", IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval","30 days target has elapsed"))

 

[NickDenyer]

Thanks for all of this help - but this does not seem to work I'm afraid regardless of what date I enter into field 2, it says '30 day target achieved'.

Any thoughts? Do I need to add the expression to any of the afterupdate sections? At the moment it is just in the control source of a text field.

Thank you!

 

[arnelgp]

how would you compute when to show "30 target has been achieved" aside from having a value on field2?

 

[NickDenyer]

My apologies! I did not explain correctly!

I have two fields, and another field which displays the messages. So I have a date in Field 1 (starts the timer) and a date in Field 2 (which effectively 'stops' the timer).

In another field I utilise the control source and would wish to have messages indicating 'x Days before approval', for the date of Field 1, and depending on the date in Field 2 '30 days target achieved' or '30 days target has elapsed'.

Hope this makes more sense! I really appreciate your help, arnelgp.

 

[arnelgp]

anyway, i just ramble our expression, see if it worked for you:

=IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval",IIf(Nz([Field2], 0) <> 0 And Nz([Field2], 0) <= Date(), "30 day target achieved","30 days target has elapsed"))

 

[NickDenyer]

Thank you for your help and apologies for the mix-up.

Unfortunately, this is still not working it still says 'x days before approval). I just can't figure out why - I'm no whizz but I see how your code makes sense. It's a tough one any thoughts?

 

[arnelgp]

give me some data list, whats on field1, field2, so we can analyse. on which data does our expression fails.

 

[NickDenyer]

So at the moment I have no data to input.

(Submission Date) Field1 - Date __/__/_____
(Approval Date) Field2 - Date __/__/_____
(Timer Message Box) Field3 - Text (this is where the expression is, in the control source

Expression works (i.e. no error messages) but simply does not 'update' when a date is added to Field 2. Field 2 is blank until a date is added.

Thank you!

 

[arnelgp]

ofcourse on new record, [field1] and [field2] is blank.

=IIF(Nz([Field1],0)=0 AND Nz([Field2],0)=0,"",IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval",IIf(Nz([Field2], 0) <> 0 And Nz([Field2], 0) <= Date(), "30 day target achieved","30 days target has elapsed")))

you have to refresh your form on after update event:

private sub form_afterupdate()
me.refresh
end sub

 

[NickDenyer]

Thanks once again for your help.

The message of "x days before approval" still shows, even after updating the expression and adding Me.Refresh in all of the AfterUpdate private subs.


perhaps this is not possible to do

Thank you, arnelgp

 

[arnelgp]

are you giving up? just try until we perfect.

=IIF(Nz([Field1],0)=0 ,"",IIf(Date()-30-[Field1]<0,Abs(Date()-30-[Field1]) & " Days before approval",IIf(Nz([Field2], 0) <> 0 And Nz([Field2], 0) <= Date(), "30 day target achieved","30 days target has elapsed")))

 

[NickDenyer]

Thank you, arnelgp

Certainly not giving up, just now feel like I'm taking advantage of your kindness!

Still it only shows number of days for approval. It just doesn't seem to change when I add a date into Field2. Very peculiar.

Thank you

 

[arnelgp]

running out of card here:

=IIF(Nz([Field1],0)=0 ,"",IIf(datediff("d",date(),dateadd("d",30,[Field1]))>0,datediff("d",date(),dateadd("d",30,[Field1])) & " Days before approval",IIf(Nz([Field2], 0) <> 0 And Nz([Field2], 0) <= Date(), "30 day target achieved","30 days target has elapsed")))

anyway its late here, we have time difference i think. so i'll leave it to anybody out there for the meantime. be back tommorow.

 

[NickDenyer]

Thank you arnelgp.

Still no luck perhaps I will have a flash of inspiration tomorrow and solve it. Not a problem, thank you for all of your help today, really appreciate it. Have added to your reputation for your insight.

All the best,

Nick

 

[arnelgp]

here is a sample i made, try playing with it. view the control source of Text11.

 

[NickDenyer]

here is a sample i made, try playing with it. view the control source of Text11.

Thank you for taking the time to do this, arnelgp! I can confirm it works! There is however, still one small thing to iron out if possible!

If date is added to Field1, the countdown timer begins, (excellent!), if a second date is added to Field2 the countdown timer stops, and displays the message '30 day target achieved' (brilliant!) But I noticed that if I add a date that is more than 30 days, it still displays '30 day target achieved'.

I could do with it saying something like '30 day target elapsed' - is this possible?

Thank you so much!

 

[arnelgp]

ok try this:

=IIf(Nz([field1],0)=0,"",IIf(Nz([field2],0)=0,IIf(DateDiff("d",Date(),[field1]+30)>0,DateDiff("d",Date(),[field1]+30) & " days before approval","30 days target has elapsed"),iif(datediff("d",[field1],[field2])<=30,"30 days target achieved","30 days target has elapsed")))