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",
 What do you return if false?),
   IIf(Date()>[Time Extention] And Date()<[Maintenance Period],"Maintenance") 
' In No 5 you use [Ending Date] not [Time Extension]
))))

[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 "MAINTENANCE"
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?

[Gari]

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

They are all TextBoxes and they all have the same settings

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.

The code is as follows

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

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

Yes, only


I was wondering if the Nz() function was appropriate. From what I read in Ms Access help, it is used only to return "0" if the related field is empty. Is it true or I am mistaken ?

[NeoPa]

I was wondering if the Nz() function was appropriate. From what I read in Ms Access help, it is used only to return "0" if the related field is empty. Is it true or am I mistaken ?

Mistaken I'm afraid.
If only one parameter passed then it will try to determine the default to use (0 or ""), but you can specify a default (as in the code I provided).
I will need to look at this in more detail. It's not obvious why this is failing atm but I'll give it some further thought when I get some time.

[NeoPa]

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

They are all TextBoxes and they all have the same settings.

The code is as follows

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

In your code you have used [Start Date] but you consistently say that it's [Starting Date].
Can you clarify this please.
If the [Ending Date] control were actually [End Date] then this would explain your problem.

[Gari]

In your code you have used [Start Date] but you consistently say that it's [Starting Date].
Can you clarify this please.
If the [Ending Date] control were actually [End Date] then this would explain your problem.

Hello NeoPa,

Maybe I was a bit confusing and I apologise for this.

So the fields in my table are as follows:

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

The code is as follows:

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

Is there any way to attach the database? Maybe this can help...

Best regards,

G.

[NeoPa]

That may be a good idea Gari.
I don't say this without first having checked through the code and I can't see why it would have that error.
To find out how to attach a copy, go through this (Check Boxes to Pass into Query) thread paying particular attention to the steps required to make it as small as possible.

[Gari]

That may be a good idea Gari.
I don't say this without first having checked through the code and I can't see why it would have that error.
To find out how to attach a copy, go through this (Check Boxes to Pass into Query) thread paying particular attention to the steps required to make it as small as possible.

Here is the database.

By the way, when reading the other thread, I've noticed that I did not precise that the form I am using is actually a subform. Those this makes any difference?

Best regards,

G.

[NeoPa]

That attach didn't work Gari.
How big is the file in MB?
Yes it did - I hadn't refreshed my browser :(

[NeoPa]

Here is the database.

By the way, when reading the other thread, I've noticed that I did not precise that the form I am using is actually a subform. Those this makes any difference?

Best regards,

G.

I expect that makes a big difference actually ;)
Nevermind, I'll have a better understanding when I get this downloaded at home tonight.

[Gari]

Thank you for support NeoPa !

I have to leave the office now so I will see your messages tomorrow.

See you !

G.

[NeoPa]

Here is the database.

By the way, when reading the other thread, I've noticed that I did not precise that the form I am using is actually a subform. Those this makes any difference?

Best regards,

G.

In fact, as all the controls (Status and all dates) are in the same sub form, there shouldn't be a problem here.

Now I have to say the instructions in the other post (which you followed very well - too well in fact) were for a larger database where I didn't need the data so much. Can you make me another copy but with the data in it (If that's very large then lose most of it but make sure you leave enough to exhibit the problem). Don't forget to Compact & Repair before Zipping it up.
Again, I can't see what may be causing this issue - even with a closer look.

When you've posted the new version I'll see if I can delete the old one. I can certainly edit the post to stop linking to it then you may be able to finish off the job in Attachment Management in your Control Panel.

[Gari]

Hello NeoPa,

Here is the database with a bit of data. I was wondering if you needed some, I should have followed my first thought ^^.

Best regards,

G.

[NeoPa]

Thanks for that Gari - I'll get on to it at home. I don't like to download items at work.

[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 "MAINTENANCE"
5. Otherwise (today must be > [Maintenance Period]) return "COMPLETION"

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

This is a quote of Post #5 which you confirmed to me was a correct reflection of the situation.
Although there is still confusion in my mind related to the [Time Extension] problem returning #Error, your data certainly doesn't match this specification. The only field in your data which consistently holds data is [Start Date]. All of the other fields are blank (Null) in some instances.
I will look further into this as it wil almost certainly mean some changes in the code but my first task will be to find and fix the issue that should be handled already in the existing code.

[NeoPa]

In your 'Relationships' window you have the 'Company' join in twice. You should remove Company_1 version as it seems superfluous.

[NeoPa]

I could not find a decent replacement for the formula (I tested that the value returned was Null but it still didn't respond as expected) so I looked at the RecordSource of the subform instead. The changes I made here are threefold :

1. Tidied up the INNER JOIN as it repeated the fields it connected on ([Account Number] - as per original Relationships).
2. Changed it from a GROUP BY query to a SELECT but qualifed by the DISTINCT predicate.
3. Added a Status field with fixed formula (handling all possible Null value dates).

SELECT DISTINCT 
    Projects.[Project Name],
    Owner.[Owner Name],
    Currency.[Currency Code],
    Projects.[Project Account Number],
    Company.[Account Number],
    Projects.Value,
    Projects.Variations,
    Projects.[Start Date],
    Projects.[Ending Date],
    Projects.[Time Extension],
    Projects.[Maintenance Period],
    IIf(Date()<[Projects].[Start Date],
        'STARTING',
        IIf(Date()<=Nz([Projects].[Time Extension],[Projects].[Ending Date]),
            'ONGOING',
            IIf(Date()<=Nz([Projects].[Maintenance Period],Date()),
                'MAINTENANCE',
                'COMPLETION'))) AS Status,
    Projects.ProjectID
FROM Owner RIGHT JOIN 
    ([Currency] RIGHT JOIN 
    (Company INNER JOIN Projects
      ON Company.[Account Number]=Projects.[Account Number])
      ON Currency.CurrencyID=Projects.CurrencyID)
      ON Owner.OwnerID=Projects.OwnerID

The ControlSource for the Status control is now simply Status.

[NeoPa]

I can only assume that the TextBox controls convert Null values to empty strings so the Nz() function would never trigger the Replacement or Default value. The solution to put the Result field within the (SQL) query instead works better anyway so there's no need to make up a long & complicated formula for the Result TextBox.

[Gari]

Dear NeoPa,

Thank you for your answer. I'll have a close look into it and come back to you as soon as possible.

Best regards,

R.

[NeoPa]

That's fine Gari.
Don't forget to go through all posts from #28 ;)

[Gari]

Thanks for reminding me ^^

It is a bit hectic at work those days so do not expect a reply from me before saturday or sunday. I tell you that as a courtesy note ^^.

Best regards,

G.

[NeoPa]

I appreciate the courtesy Gari :)
It really does make a difference if members reply - even if - as yours - it's just a coutesy note explaining an expected absence. It reminds one that we're dealing with real people ;)
I won't expect anything more before the weekend but will get a trigger if you (or anyone else) does post in here.

-NeoPa

[Gari]

Dear NeoPa,

I took the time to finally go through your posts. Here are my replies

The only field in your data which consistently holds data is [Start Date]. All of the other fields are blank (Null) in some instances.

Actually, in an ideal situation, the only field that could be Null is [Time Extension]. But I wanted to test all the possibilities in the code. I guess I was confused between the "ideal" situation that I want to happen, and the day-to-day issue that I might face while running the database. Anyway, sorry for that.

In your 'Relationships' window you have the 'Company' join in twice. You should remove Company_1 version as it seems superfluous.

I've noticed that but surprisingly, I always remove this relationship but still it appears back. There must be some redundancies in the code that I have to go through.

I could not find a decent replacement for the formula (I tested that the value returned was Null but it still didn't respond as expected) so I looked at the RecordSource of the subform instead. The changes I made here are threefold :

Thank you very much for your code, it works really well ! And what I like about it is that it is "clean".

Regarding:

Changed it from a GROUP BY query to a SELECT but qualifed by the DISTINCT predicate.

Can you explain this for me? Being new in access, I don't understand the subtility of it. (I could have not asked you this because your code works perfectly but I like to understand the things I'm doing. It enables me to reproduce them after.)

I can only assume that the TextBox controls convert Null values to empty strings so the Nz() function would never trigger the Replacement or Default value.

Maybe an IIf function stating to replace "" to Null would have worked also? But your code is clearer.


By the way, I want to add a check box that when checked, will allow the form to return "COMPLETED", that is to say that completion has been validated by the user and the project fully achieved.

I have thought of someting like this:

 IIf(Date()<[Projects].[Start Date],
        'STARTING',
        IIf(Date()<=Nz([Projects].[Time Extension],[Projects].[Ending Date]),
            'ONGOING',
            IIf(Date()<=Nz([Projects].[Maintenance Period],Date()),
                'MAINTENANCE',IIf([Checkbox]=True,'COMPLETED',
                'COMPLETION')))) AS Status,

I have not tested it yet (I am working on this database on my freetime only). But what do you think ?

Thank you for all your help.

Best regards,

G.

[NeoPa]

Dear NeoPa,

I took the time to finally go through your posts. Here are my replies

The only field in your data which consistently holds data is [Start Date]. All of the other fields are blank (Null) in some instances.

Actually, in an ideal situation, the only field that could be Null is [Time Extension]. But I wanted to test all the possibilities in the code. I guess I was confused between the "ideal" situation that I want to happen, and the day-to-day issue that I might face while running the database. Anyway, sorry for that.

Not a problem.
Over time one gets to realise that precision in database work is more than desirable - it's an absolute necessity.
Many people never get that but if this has helped you to appreciate that then it's worth its weight in gold :)

In your 'Relationships' window you have the 'Company' join in twice. You should remove Company_1 version as it seems superfluous.

I've noticed that but surprisingly, I always remove this relationship but still it appears back. There must be some redundancies in the code that I have to go through.

I'll leave this one with you. With the relationships set up precisely, your other work will be easier and less prone to problems.

I could not find a decent replacement for the formula (I tested that the value returned was Null but it still didn't respond as expected) so I looked at the RecordSource of the subform instead. The changes I made here are threefold :

Thank you very much for your code, it works really well ! And what I like about it is that it is "clean".

For me - that's the highest form of praise. So, thank you for that.

I'll get to the other points when I can as they both take a little more to reply to.

[NeoPa]

Regarding:

Changed it from a GROUP BY query to a SELECT but qualifed by the DISTINCT predicate.

Can you explain this for me? Being new in access, I don't understand the subtlety of it. (I could have not asked you this because your code works perfectly but I like to understand the things I'm doing. It enables me to reproduce them after.)

Firstly: I'm very pleased to explain and I like your attitude.
Secondly:
Using GROUP BY is a way to group records together, generally to produce aggregates (Sum; Average; Count; etc), which are not to be found in the original data directly. It can also, of course, be used to lose duplicated data, but it is not a very efficient way to do this. DISTINCT and DISTINCTROW are predicates of the SELECT clause which allow duplicate data to be ignored (dropped from the result set) in a more direct and efficient way.

[NeoPa]

I can only assume that the TextBox controls convert Null values to empty strings so the Nz() function would never trigger the Replacement or Default value.

Maybe an IIf function stating to replace "" to Null would have worked also? But your code is clearer.

That would have muddied the waters more than helped the situation I expect.
The reason that the Null can be so much cleaner is because Nz() can be used instead of IIf().

[NeoPa]

By the way, I want to add a check box that when checked, will allow the form to return "COMPLETED", that is to say that completion has been validated by the user and the project fully achieved.

I have thought of someting like this:

 IIf(Date()<[Projects].[Start Date],
        'STARTING',
        IIf(Date()<=Nz([Projects].[Time Extension],[Projects].[Ending Date]),
            'ONGOING',
            IIf(Date()<=Nz([Projects].[Maintenance Period],Date()),
                'MAINTENANCE',IIf([Checkbox]=True,'COMPLETED',
                'COMPLETION')))) AS Status,

I have not tested it yet (I am working on this database on my freetime only). But what do you think ?

Thank you for all your help.

Best regards,

G.

Your test will succeed.
However, I've made a slight amendment both to the code and the layout (hopefully to make it easier to read and understand).

    IIf(Date()<[Projects].[Start Date],
        'STARTING',
        IIf(Date()<=Nz([Projects].[Time Extension],
                       [Projects].[Ending Date]),
            'ONGOING',
            IIf(Date()<=Nz([Projects].[Maintenance Period],Date()),
                'MAINTENANCE',
                IIf([Checkbox],'COMPLETED','COMPLETION')))) AS Status,

The beauty of boolean type fields is that it is unnecessary to compare them to anything to get the result.
It is also a better policy as the value (True) is equivalent to -1, but any non-zero value is treated as TRUE.
This means, in some cases that TRUE =/= True. EG. intMyResult = 5: If intMyResult Then {Treated as TRUE} whereas If intMyResult = True {Treated as False as 5 =/= -1}. This will not affect boolean type fields as they can only hold True or False (and sometimes Null in 3-way controls).