Calculated Field problem

[paulquinlan100@hotmail.com]

Hi

Can anyone spot an obvious problem with the following calculated field
on a report. Im trying to use the field [End Date] only when it
contains data (which it normally doesnt)

=(DateDiff("d", (IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])/7)*[Weekly Impact]*-1

I'm just getting a blank field, or alternatively a better way of
getting the result

Thanks for any suggestions
Paul

[Dvae Griffiths]

Re: Calculated Field problem

Hi Paul

I am no expert here with Access I have just a good background in
programming.

Is this supposed to be

IIf([End Date] Is Null,#31/03/2007#

or

If([End Date] Is Null,#31/03/2007#

Just my observation.

Maybe it helped.

DaveG


paulquinlan100@ hotmail.com wrote:
[color=blue]
> Hi
>
> Can anyone spot an obvious problem with the following calculated field
> on a report. Im trying to use the field [End Date] only when it
> contains data (which it normally doesnt)
>
> =(DateDiff("d", (IIf([End Date] Is Null,#31/03/2007#, [End
> Date])),[Forecast Date])/7)*[Weekly Impact]*-1
>
> I'm just getting a blank field, or alternatively a better way of
> getting the result
>
> Thanks for any suggestions
> Paul[/color]

--

[Rich P]

Re: Calculated Field problem

One thing you could try is to break your expression apart and just get
one value at a time. If that expression works then add the next
expression to it.
[color=blue][color=green]
>>=(DateDiff("d ",(IIf([End Date] Is Null,#31/03/2007#, [End[/color][/color]
Date])),[Forecast Date])/7)*[Weekly Impact]*-1

try enclosing the -1 in parentheses. I think the code is interpreting
this

[weekly impact] * - 1

instead of

[weekly impact] * (-1)

Or

(DateDiff("d",( IIf([End Date] Is Null,#31/03/2007#, [End
Date])),[Forecast Date])
/7)

you are getting a difference of days and dividing that by 7. Then you
are multiplying this value by a value contained in [weekly impact]

*[Weekly Impact]

then it appears you want the total value to be negative. If you enclose
(-1), then this is what the computer would interpret.

*
(-1)

Try breaking each expression apart to see if you get the desired value.
Start with the difference of days. If you get the correct result then
try adding the divide value by 7 part. If that gives a correct result
then add the * [weekly impact]...


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Wannall]

Re: Calculated Field problem

Change iif([End Date] Is Null, ...

Make it iif(IsNull([End Date)=True, ...

The "=true" is not strictly necassary, since IsNull() returns only true or
false anyway, but I have grown fond of seeing explicit comparison to False
or True instead of having to decode what a function returns when I'm
actually trying to figure out something else.

[paulquinlan100@hotmail.com]

Re: Calculated Field problem

Thanks a lot Rich! The (-1) did the trick!

Paul


Rich P wrote:[color=blue]
> One thing you could try is to break your expression apart and just get
> one value at a time. If that expression works then add the next
> expression to it.
>[color=green][color=darkred]
> >>=(DateDiff("d ",(IIf([End Date] Is Null,#31/03/2007#, [End[/color][/color]
> Date])),[Forecast Date])/7)*[Weekly Impact]*-1
>
> try enclosing the -1 in parentheses. I think the code is interpreting
> this
>
> [weekly impact] * - 1
>
> instead of
>
> [weekly impact] * (-1)
>
> Or
>
> (DateDiff("d",( IIf([End Date] Is Null,#31/03/2007#, [End
> Date])),[Forecast Date])
> /7)
>
> you are getting a difference of days and dividing that by 7. Then you
> are multiplying this value by a value contained in [weekly impact]
>
> *[Weekly Impact]
>
> then it appears you want the total value to be negative. If you enclose
> (-1), then this is what the computer would interpret.
>
> *
> (-1)
>
> Try breaking each expression apart to see if you get the desired value.
> Start with the difference of days. If you get the correct result then
> try adding the divide value by 7 part. If that gives a correct result
> then add the * [weekly impact]...
>
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

[paulquinlan100@hotmail.com]

Re: Calculated Field problem

Thanks a lot Rich! The (-1) did the trick!

Paul


Rich P wrote:[color=blue]
> One thing you could try is to break your expression apart and just get
> one value at a time. If that expression works then add the next
> expression to it.
>[color=green][color=darkred]
> >>=(DateDiff("d ",(IIf([End Date] Is Null,#31/03/2007#, [End[/color][/color]
> Date])),[Forecast Date])/7)*[Weekly Impact]*-1
>
> try enclosing the -1 in parentheses. I think the code is interpreting
> this
>
> [weekly impact] * - 1
>
> instead of
>
> [weekly impact] * (-1)
>
> Or
>
> (DateDiff("d",( IIf([End Date] Is Null,#31/03/2007#, [End
> Date])),[Forecast Date])
> /7)
>
> you are getting a difference of days and dividing that by 7. Then you
> are multiplying this value by a value contained in [weekly impact]
>
> *[Weekly Impact]
>
> then it appears you want the total value to be negative. If you enclose
> (-1), then this is what the computer would interpret.
>
> *
> (-1)
>
> Try breaking each expression apart to see if you get the desired value.
> Start with the difference of days. If you get the correct result then
> try adding the divide value by 7 part. If that gives a correct result
> then add the * [weekly impact]...
>
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]