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.

[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 "MAINTENANC E"
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.