Return text with IIf function

[Gari]

Hello,

I have Four fields:

[Starting Date]
[Ending Date]
[Time Extension]
[Maintenance Period]

All are on a date format.

I want to input a text on a text box field doing the following:

1) If today’s date < [Starting Date], then return “STARTING”
2) If [Starting Date] < today’s date < [Ending Date] then return “ONGOING”
3) If [Starting Date] < today’s date < [Time Extension] then return “ONGOING”
4) If [Time Extension] is Null then if [Ending Date] < today’s date < [Maintenance Period] then return “MAINTENANCE”
5) If [Time Extension] is not Null then if [Ending Date] < today’s date < [Maintenance Period] then return “MAINTENANCE”
6) If today’s date > [Maintenance Period], then return “COMPLETION”

Later, I will want the message “COMPLETED” to appear, when the user will have ticked a checkbox confirming the completion.

Going step by step with the Expression builder, I have built so far this:

=IIf(Date()<[Start Date],"Starting",IIf(Date()>[Start Date] And Date()<[Ending Date],"Ongoing",IIf(Date()>[Start Date] And Date()<[Time Extention],"Ongoing",IIf([Time Extention]=Null,IIf(Date()<[Maintenance Period] And Date()>[Ending Date],"Maintenance"),IIf(Date()<[Maintenance Period] And Date()>[Time Extention],"Maintenance")))))

It works fine with 1), 2), 3), 5), but to my surprise it did not work with 4).

Regarding 6), I did not go across it yet as I did not resolve the problem for 4).

If anyone can help…

Thanking you in advance,

G.

[NeoPa]

You don't don't say what you'd like to see if any of the dates are equal to today.
You don't say whether any of the stored date fields contain time elements with the date.
Otherwise, I think this is a reasonable straightforward question and I would expect an answer shortly.
If you haven't got anything by tomorrow, bump the thread and we'll see what we can do for you.

[Gari]

You don't don't say what you'd like to see if any of the dates are equal to today.

That is a good remark and actually it did not come to my mind. My database is for following up projects that typically will run over months. So if any of the dates are equal to today, I think I can just go along with replacing '<' by '<='.

You don't say whether any of the stored date fields contain time elements with the date.

There are no time elements in the date fields. Only dates on a dd/mm/yyyy format.

Otherwise, I think this is a reasonable straightforward question and I would expect an answer shortly.
If you haven't got anything by tomorrow, bump the thread and we'll see what we can do for you.

Thank you for your help.

Best regards,

G.

[MMcCarthy]

=IIf(Date()<=[Start Date],"Starting",
IIf(Date()>[Start Date] And Date()<[Ending Date],"Ongoing",
IIf(Date()>[Start Date] And Date()<[Time Extention],"Ongoing",
IIf(IsNull([Time Extention]),
   IIf(Date()>[Ending Date] And Date()<[Maintenance Period],"Maintenance",
[b][i]What do you return if false?[/i][/b]),
   IIf(Date()>[Time Extention] And Date()<[Maintenance Period],"Maintenance") [i][b]
' In No 5 you use [Ending Date] not [Time Extension][/b][/i]
))))

[NeoPa]

Gari,
You need to look at your question a little more.
I'm assuming that your requirements are better expressed as (Please notice where I have included & omitted the '='s in my version.) :

I have four Date fields (no time elements) :

• [Starting Date]
• [Ending Date]
• [Time Extension]
• [Maintenance Period]

All must contain values except [Time Extension] which may be empty. Otherwise the dates are in progressive order.
I have a control which needs to show a string determined by the following logic :

1. Treat [Time Extension] as [Ending Date] if it is not entered.
2. If today < [Starting Date], then return "STARTING"
3. Otherwise if today <= [Time Extension] then return "ONGOING"
4. Otherwise if today <= [Maintenance Period] then return "MAINTENANC E"
5. Otherwise (today must be > [Maintenance Period]) return "COMPLETION "

Please can you confirm that this is correct and not a wrong guess.

[Gari]

Dear both,

Thank you very much for your remarks. Thanks to them, I have built the following line of code:

=IIf(Date()<=[Start Date],"Starting",
IIf(Date()>[Start Date] And Date()<= [Ending Date],"Ongoing",
IIf(Date()>[Start Date] And Date()<= [Time Extention],"Ongoing",
IIf(IsNull([Time Extention]),
IIf(Date()>[Ending Date] And Date()<= [Maintenance Period],"Maintenance","Completion"),
IIf(Date()>[Time Extention] And Date()<=[Maintenance Period],"Maintenance","Completion")))))

From what I have tested, it seems to work correctly. I think it is a good assumption that:
If Date() is >=[Start Date]
And If Date()>=[Ending Date] Or If Date()>=[Time Extention]
And If Date()>=[Maintenance Period]
Then the project is obviously completed and the formula should return "Completion ".

Regarding the Checkbox thing (I did not yet set up this option), I think that in the code, instead of "Completion ", I will put a line as follows:

IIf([Checkbox]=True,"Completed","Completion")

Thank you for your feedback.

Best regards,

G.

[NeoPa]

Gari,
I await a response to my last post.

Your code may work but could do with a lot of tidying up (redundant code will only confuse you later on). Unfortunately, to do this properly I need confirmation of what you're actually asking.
-Adrian.

[NeoPa]

Alternatively, if you're not interested, please let me know anyway - just a courtesy note.

[Gari]

Dear NeoPa (or Adrian ??),

Sorry to not have answered your post directly.

Your assumption is right : that is my requirement.

I wanted actually to test your sequence but I was confused by the way of writing line 1 (Treat [Time Extension] as [Ending Date] if it is not entered) (I am quite new in programming), so I came back to a code nearer to the one proposed by mmccarthy.

But as I am always eager to learn, I am greatly interested by your feedback.

Thank you and best regards.

G.

[NeoPa]

Gari,
You can refer to me as either. My real name is public knowledge on this site now so ...
My reasoning for that line was to try to clarify the underlying logic of the request before moving on to a solution. Rather than complicated Ifs & Elses when refering to the [Time Extension] & [Ending Date] fields, I wanted to see them as a single entity where [Ending Date] is the original planned end date of the project but allowances can be made where necessary to extend this, giving [Time Extension]. For the purposes of your code (logic) however, Whenever we compare [Time Extension], we should always use [Ending Date] if [Time Extension] is not entered.
That's a more longwinded way of saying it but hopefully clearer.

The code proposed by Mary, a top contributor in this forum, is absolutely correct in that it matches your question directly. However, I think a tidying up of the question can produce simpler (easier to understand) code.
I will go off now and try to produce something a little more direct. It will work on the reliable assumption that previous lines already test for less than the value.

[NeoPa]

The following code should be included as a single line.
I'm breaking it up to make it easier to read on here.

=IIf(Date()<=Nz([Time Extension],[Ending Date]),
IIf(Date()<[Starting Date],"STARTING","ONGOING"),
IIf(Date()<=[Maintenance Period],"MAINTENANCE","COMPLETION"))

NB. The < & <= are important and currently in the correct places.

[Gari]

Hello NeoPa,

I have tried your code but it did not work properly: I am getting an #Error message when I have no [Time Extension] date entered.

Is there something I should configure first?? I am using Access 2002 in case the info is needed.

Best regards,

G.

[NeoPa]

Gari,
Can you let me have the setting of the Required property for the [Time Extension] field as set up in your table please (I wouldn't exepct an error there).
Also the full error message might help too - to give me somewhere to start looking.
Also, as an aside, can you follow the logic of the code ok? It's important that you understand what's in your database for future changes or use whatever.

[Gari]

Hello NeoPa,

The Required field is set to "No", as for the other dates.

There is no full error message: when the field [Time Extension] is empty, it simply returns back "#Error" in the text box.

And for the aside: Yes, I understood the logic of the code ^^.

Please do not hesitate to revert back to me should you need more information.

Best regards,

G.

[NeoPa]

1. These fields (below), are they TextBoxes on your form too?
[Starting Date]
[Ending Date]
[Time Extension]
[Maintenance Period]

2. Can you post here the exact code you are using which returns #Error. No CRs or LFs, just the code exactly as you have it in your TextBox control.

3. Does it only result in #Error when the [Time Extension] field is empty?