I've set up a query to report Performance Indicators on my database.

The fields are as follows:
Ref
Premises
Paperwork Received
Date Received
Sufficient Paperwork Received
Date Outstanding Paperwork Received
Sent to Education
Received from Education
Education Turnaround Time
Date Comments Returned
Turnaround Time

I have set up a sum to calculate the Education Turnaround Time and that works fine.

I have then basically used the following calculation:
[Date Comments Returned]-[Date Received]-[Education Turnaround Time]
and this works well.

The problem I have is sometimes the calculation needs to be based on
[Date Comments Returned]-[Outstanding Information Received]-[Education Turnaround Time]. This must only be used when the Outstanding Info field is populated.

I'm sure this must be fairly simple to all you SQL experts out there, but I'm fairly new to Access and my knowledge of code is limited.

Where are you executing the query from?

Kevin

dartcoach

I've entered the calculation directly into the control source of a report.

I would try checking to see if the Outstanding Info is null, if it is, use your current calculation else use the other one.

Kevin

dartcoach

How would I go about asking if the Outstanding Info field is null before it carrying out the original calculation?

I would use the IIF function.
In you control source:

=IIf(isnull(me.Outstanding info,(Calc1),(Calc2)

if Outstanding Info is null, it carries out calc1 otherwise it carries out calc2.

Use the help for IIF to insure you utilize the correct syntax.

Kevin

dartcoach

Thanks very much for the help - I'll let you know if it works.