Access Query Needs Small Change

[gilly]

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?

[phinds]

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

[gilly]

brillant thank you phind!! It works great.

Any one know how to add in the

SELECT AVG(NoOfDays)AS AvgDays FROM QueryInvoiceList;

Ive added it in like this..... but get a syntax error

Many Thanks

UPDATE QueryInvoiceList SET NoOfDays = DateDiff("d",[DateOnQuery],IIf(IsNull([DateReturned]),Date,[DateReturned])) AND SELECT AVG(NoOfDays)AS AvgDays FROM QueryInvoiceList;

[gilly]

The query below returns the Number of days difference between two date fields.

I now need to derive an average from the number of days. Id like to display this average in a message box. Is this possible within my exisitng query.

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


Any ideas?
I received this access query average code below but cant get it to work...

SELECT AVG(NOOfDays)AS AvgDays FROM QueryTable

[Killazzz]

Shouldn't there be a space between the closing brace and AS
SELECT AVG(NoOfDays)AS AvgDays FROM QueryInvoiceList;

SELECT AVG(NoOfDays) AS AvgDays FROM QueryInvoiceList;

I'm also guessing that an update and select query can't be executed in one shot.

J