Find Average

[ecuevas]

I need to make a query which counts the number of days between "Date of Complaint" and "Effective Date". This is what I have so far:

[SELECT [Customer Complaint Log].[Complaint Number], DateDiff("d",[Date of Complaint],[Effective Date]) AS Expr1
FROM [Customer Complaint Log]
WHERE ((([Customer Complaint Log].[Effective Date]) Is Not Null) AND (([Customer Complaint Log].[Date of Complaint]) Is Not Null));



I need to make it so that it uses todays date for "Effective Date" if there is no "Effective Date". Any help would be appreciated. Thanks.

 

[pbaldy]

Try

Nz(EffectiveDate, Date())

 

[ecuevas]

I figured it out. I used:

SELECT [Customer Complaint Log].[Complaint Number], [Customer Complaint Log].[Date of Complaint], DateDiff("d",[Date of Complaint],[Effective Date]) AS Expr1
FROM [Customer Complaint Log]
WHERE ((([Customer Complaint Log].[Date of Complaint]) Is Not Null) AND (([Customer Complaint Log].[Effective Date]) Is Not Null)) OR ((([Customer Complaint Log].[Date of Complaint]) Is Not Null) AND ("IF" Is Null) AND ("Then"=Date()));

I made another query which asks for 2 dates and gives you the average between those dates.This is what I have for the average query:

SELECT Avg([Expr1]) AS Expr2
FROM [Response Time-COUNT], [Customer Complaint Log]
WHERE ((([Customer Complaint Log].[Date of Complaint]) Between [Begin Date] And [End Date]));

No matter what days I type in it keeps giving me the average for ALL of the numbers. Does anyone know why this is happening?

 

[ecuevas]

Nevermind I fugured it out. I put the parameter values in the COUNT query instead of the AVERAGE query.