PDA

View Full Version : Convert Excel Formula into Access

cgavin
05-14-2007, 04:41 AM
Hi All,

I am doing an Access report and for one of the fields I am required to do a "fairly" complicated formula whose results change depending on other values stored in other fields on the form.

The Excel formula is this:

=IF(A7="Completed",IF(OR(K7="Cost Avoidance vs Budget",K7="Once-Off"),0,M7*(($M$1)-J7)/365),"")

I tried to convert it into a Report formula like so:
= IFF ( [IMPORTANCE] = "Complete"
,IFF [Category] = "Cost Avoidance vs Budget,
"0",
IFF [Category] = "Once-Off",
"0",
([Projected Group Benefit] * (((31/12/2007) - [Contract Date]) / 365)),
"")

But it doesn't seem to work. I'd really appreciate any help on this you could give.

Cheers,
Colm
john471
05-14-2007, 06:36 AM
This might get you closer, but I'm not certain about the 'date - math' aspect of it. Looking up "DateDiff" and/or "CDate" in help may prove fruitful for you.

The OR construct/useage in Excel is not the same in Access...

= IFF ( [IMPORTANCE] = "Complete",
IFF([Category] = "Cost Avoidance vs Budget" OR [Category] = "Once-Off","0",
([Projected Group Benefit] * (((31/12/2007) - [Contract Date]) / 365)),
"")
boblarson
05-14-2007, 07:14 AM
= IFF ( [IMPORTANCE] = "Complete",
IFF([Category] = "Cost Avoidance vs Budget" OR [Category] = "Once-Off","0",
([Projected Group Benefit] * (((31/12/2007) - [Contract Date]) / 365)),
"")

By the way, the immediate if function is IIf, not IFF.
boblarson
05-14-2007, 07:21 AM
= IFF ( [IMPORTANCE] = "Complete",
IFF([Category] = "Cost Avoidance vs Budget" OR [Category] = "Once-Off","0",
([Projected Group Benefit] * (((31/12/2007) - [Contract Date]) / 365)),
"")

By the way, the immediate if function is IIf, not IFF.

You should use DateDiff instead of just subtracting:


= IIf([IMPORTANCE] = "Complete",
IIF([Category] = "Cost Avoidance vs Budget" OR [Category] = "Once-Off","0",
[Projected Group Benefit] * (DateDiff("d", DateValue("31/12/2007"), [ContractDate]) / 365)),"")
cgavin
05-23-2007, 01:19 AM
= IIf([IMPORTANCE] = "Complete",
IIF([Category] = "Cost Avoidance vs Budget" OR [Category] = "Once-Off","0",
[Projected Group Benefit] * (DateDiff("d", DateValue("31/12/2007"), [ContractDate]) / 365)),"")

Thanks for your help Bob and John, this is the one that worked for me.

Much appreciated,
Colm

PS Sorry about the delay in replying