Calculate fields only if another is Null

[hobbit2612 via AccessMonster.com]

Hi,

I wonder whether someone could help me please.

I have a query field where I perform a calculation of the difference between
two date fields, to be exact, when a report is expected to be returned and
the date I run the query i.e the current date. However I only want to make
this calculation if a third field, date report received is blank.

I have tried writing the calculation below, but all I get is that there are
an 'Incorrect number of arguments.'


IIf(IsNull([Date Report Returned], DateDiff(Date () - [Date Report Expected])

Could anyone shed a bit of light on where I maybe going wrong please.

Many thanks

Regards

 

[John Spencer]

You are missing a few closing parentheses. The number of left and right
parens must be the same.
In addition the DateDiff function takes three arguments separated by commas
(or perhaps semi-colons if you are in non-US countries).
First argument is what are you calculating - Years, Months, Days, Hours,
Minutes, Seconds.
Second argument is first date
Third argument is the second date

Putting all that together, you may want something like:

IIf(IsNull([Date Report Returned]), DateDiff("D",Date () , [Date Report
Expected]))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[hobbit2612 via AccessMonster.com]

John,

Thanks very much for this. I had an idea I might be on the right track, but
thanks to you it works a treat and it gives me exactly what I need and, by
the way you've broken it down I understand the elements of the expression a
lot better.

Many thanks for your time and trouble, much appreciated.

Regards Chris

You are missing a few closing parentheses. The number of left and right
parens must be the same.
In addition the DateDiff function takes three arguments separated by commas
(or perhaps semi-colons if you are in non-US countries).
First argument is what are you calculating - Years, Months, Days, Hours,
Minutes, Seconds.
Second argument is first date
Third argument is the second date

Putting all that together, you may want something like:

IIf(IsNull([Date Report Returned]), DateDiff("D",Date () , [Date Report
Expected]))

Hi,

[quoted text clipped - 18 lines]

Regards