Help with a field calculation please

[stuart_adair]

Hi,

I have a database with fields called:

Date Raised (Date which defaults to todays date)
Target Closure Date (Date calculated)
Priority_2 (Tick box)

Currently the Target Closure Date is automatically calculated by an entry in the default value on the form which reads [Date Raised] +5 and this works fine.

I've been asked to add a formula in so if Priority_2 is ticked then the Target Closure Date = Date Raised +1 day. If Priority_2 is not ticked then the Target Closure Date = Date Raised + 5 days.

I'm struggling with this; my Access experience isn't brilliant so please be gentle with me

Stu

 

[Isskint]

You pretty much done it in your post. Use the Iif() function. The formula you would need is;

iif([Priority_2],[Date Raised]+1,[Date Raised]+5)

 

[MarkK]

But then an interesting thing occurs. If DateRaised and Priority are both specified, then you don't need to store the ClosureDate, because the ClosureDate, by definition--a known and fixed calculation--is a function of the other two data points. If you store all three, then you are always forced to update the dependency, and you can--and one day you will--have a condition where your data is in conflict with itself, which is evil.

Food for thought,

 

[stuart_adair]

Thank you for your replies.

Mark you are quite correct in your logic, however as the field already exists and I use if for reports further down the line I'm keen not to change things.

Isskint - Alas your suggestion didn't work. I think I know why (Forgive if I'm overstepping the mark) but when the form opens the default value for the priority is No so the Closure Date is then calculated at 5 days. When I change the status of the Priority the Closure date isn't recalculated.

Stu

 

[stuart_adair]

Ps: Tried to add the formula you gave me to the 'After update' event box on the Priority drop down but I get a message "Compile error expected ="

 

[stuart_adair]

Hope you're not fed up with me yet. I've tried this in the 'after update' box on Priority which gives me a type mismatch error after I update the Priority box. Its progress as I'm getting a formula to run but I've obviously got the syntax wrong. This is what I'm using ....= iif([priority_2]=-1,[Target closure date]=[Date raised]-1,[Target closure date]=[Date raised]-5)

 

[MarkK]

The IIf() function returns a value, doesn't execute an operation, so see how this differs? . . .

ClosureDate = iif([priority_2]=-1,[Date raised]-1,[Date raised]-5)

 

[stuart_adair]

Thanks Mark. Where should I put that code, in the 'After update' of the Priority field ?

 

[MarkK]

Well, this is the trouble I raised earlier. Everywhere that either of those datapoints are updated, now require that you update the third point. Right? So AfterUpdate on DateRaised and AfterUpdate on Priority, and that needs to occur everywhere in your system where that data might be updated.