limit answer in access calculated field

[rncr]

Hi, I have a calculated field in access form which computes the percent progress:

The formula I have used is:

Percent Progress = (Date()-([Planned Start Date]-1))/[Activity Duration]

and this working fine.

But I want the answer to stop at 100% when it reached 100%.. and will not continue as days increasing it will reached 200%, 300%, etc higher.

Anyone who could help me please..

[Narender Sagar]

One of the option is using IIf function.
If Percent progress is more than 100%, Value if True: "100%", Else : show Percent Progress itself.

NewPercent: IIf([PercentProgress]>100%,"100%",[PercentProgress])

So you can hide main formula in the form and show only NewPercent

[NeoPa]

You don't explain the logic behind your formula, so that just makes it harder to explain the answer to you.

Use IIf() to check the Date() value is < that value which would indicate 100% in your logic. If that isn't clear then you need to work harder at making your question make sense before you post it.

[rncr]

Thank you, Narender. Got your point, thanks for the idea. It is now working so fine. Thanks a lot!

[Narender Sagar]

Can you please inform, what exactly you have written and where? You can do do it in a query.
Sorry there is missing brackets in the formula I have given :

NewPercent: IIf([PercentProgress]>100%,"100%",[PercentProgress])

Just place a ( after IIF

[rncr]

Sorry Narender, I have just forgot to change the word "Percent Progress" as I have saved it differently. It is now working fine.. Thanks a lot for your help

[Narender Sagar]

Good to know its working.. :)

[rncr]

Hi Narender, I would like to get your kind help for the database I am creating now.. I have a field "Action" which is a look-up wizard and will type later the values I want. What I need is, to track all the action of a person. For example I would make a record for Person A then I would need list down all actions needed for him. I would like to use the field "Action" many times. I like to put command button beside "Action" then when I click it, it will add below the field "Action" then I can input into it, repeating this how many time and so Person A will have multiple actions on the record. I have tried to create different fields namely Action1, Action2, Action3, etc but then in the end I don't know how I can create report to show only those pending action/s per Person

[Narender Sagar]

Dear rncr,
I and other members of Bytes, will definitely help you, but as per site rule, you should start new thread for your new question. That will be helpful for other people also, who will search for similar kind of problem.
hope you understand this.
regards..

[rncr]

no worries, already posted new thread here: http://bytes.com/topic/access/answer...ut#post3720191. thank you