I have written a query within Access as follows:

UPDATE QueryInvoiceList SET NoOfDays = DateDiff("d",IIf(IsNull([DateReturned]),Date,[DateReturned]),[DateOnQuery])

It returns the number of days between datereturned and dateon query fields. It returns negative values i.e - 8 days. I need it to return postive values i.e. 8 days. I have written it the wrong way around and need to change it so that DateOnQuery minus DateReturned returns the number of days. If datereturned is null i want to use todays date.


I also have the following query which i need to add to the Update query above. It should calculate the average number of days.

SELECT AVG(NoOfDays)AS AvgDays FROM QueryInvoiceList;

Im not sure where to insert this, i keep getting a syntax error.

Any Access Query experts out there?