Average Data In A Single Month Using Calculated Control

[AprilM]

Hello and thank you in advance for your assistance.

I have a database that tracks the results of audits that my department performs and also produces the reports. One report we provide is a monthly status review that shows all the sites compliance to regulations based on the results of the audits. I am trying to show the average of effectiveness (aka CAE) for the reports that were issued in the previous month in a text box / calculated control. The report is based off a Form that the user selects the sites and update data to add to the monthly report, which is stored in a query.

The fields I have in this query are as follows:

CAE
FacilityTypeID


Reporting
ReportDate

Here is a formula /Control Source that a co-worker did for me that is more expierienced in MS Access than me, but I am getting a #Error when the report is run.

=DAvg("[CAE]","qryBASCMonthlyReport1","(([FacilityTypeID] = 1)) AND ([Reporting]) AND ([ReportDate] = month(dLookup('ReportMonth','ReportDetails'))) AND (year([ReportDate]) = year(dLookup('ReportMonth','ReportDetails')))")

Any tips, hints, ideas would be appreciated. I have spent the last four days working on this.

AprilM

[Robeen]

Could you post the SQL of your query here?

Also:
Where is that formula being used?
Where are you seeing the #Error?

[AprilM]

I do not have SQL for this function or report just the formula that is in a Text Box on the report. The #Error is showing up where the text box is at on the report once I hit the 'View' button.

[Robeen]

1.
You said:

The fields I have in this query are as follows:

CAE
FacilityTypeID
Reporting
ReportDate

It is the SQL for this query that I asked for.

2. What is the Data Source for the report? Is it a Query . . . a Table . . .? I thought you said the report is based on a query.

I don't remember ever putting formulae in reports - so i cannot advise you about how that would work.
I usually let Access reports do certain calculations [Average, Count, Total . . .] for me - based on data that is in columns in the report.

I have used the kind of formula you have in that TextBox - in VBA. I don't know if a TextBox on a report is an appropriate place for the calculation that formula is attempting.

If you give me more details, I might be able to advise you on the best place for that calculation to be done.

Can you not just have the Report do your Average for you?

[AprilM]

Robeen,

Sorry about that, here is the SQL that is in the Form that the user will select data from for the report. This isn't something I created (I'm just beginning in SQL) but someone else who knows SQL did for me.

Private Sub ChangeReportDate()
    Dim strSQL As String
    Dim rsDate As DAO.Recordset
    
    If Not IsDate(Me.LastAudit) Then
        Me!ReportDate = ""
        Exit Sub
    End If
    
    Rem Look up report based on audit start date selected
    
    strSQL = "SELECT Facility.Name, IIf((Count(IIf([Phase]='Documentation',True))>0) And (Count(IIf([Phase]='OnSite',True))>0),IIf((IIf(1-((Count(IIf([Score]<1 And [Phase]='Documentation',True)))/(Count(IIf([Phase]='Documentation',True))))<0.85 Or 1-((Count(IIf([Score]<1 And [Phase]='OnSite',True)))/(Count(IIf([Phase]='OnSite',True))))<0.85,'True','False'))='True',IIf(1-((Count(IIf([Score]<1 And [Phase]='Documentation',True)))/(Count(IIf([Phase]='Documentation',True))))<0.65 Or 1-((Count(IIf([Score]<1 And [Phase]='OnSite',True)))/(Count(IIf([Phase]='OnSite',True))))<0.65,'High Risk','Med Risk'),'Low Risk'),'') AS RiskRating, AuditMain.CAE, Facility.FacilityID, AuditMain.AuditReportDate, AuditMain.AuditStart " & _
                "FROM ((AuditMain INNER JOIN Facility ON AuditMain.AuditFacilityID = Facility.FacilityID) INNER JOIN BasicAuditResults ON AuditMain.AuditID = BasicAuditResults.AuditID) INNER JOIN BasicChecklist ON BasicAuditResults.ChecklistID = BasicChecklist.ID " & _
                "GROUP BY Facility.Name, AuditMain.CAE, Facility.FacilityID, AuditMain.AuditReportDate, AuditMain.AuditStart " & _
                "HAVING (((Facility.FacilityID) = " & Str(Me.FacilityID) & ") AND ((AuditMain.AuditStart) = #" & Me.LastAudit & "#));"
    
    
    
    Rem strSQL = "SELECT AuditMain.AuditReportDate, AuditMain.AuditFacilityID, AuditMain.AuditStart, AuditMain.CAE " & _
                "FROM AuditMain " & _
                "WHERE (((AuditMain.AuditFacilityID) = " & Str(Me.FacilityID) & ") AND ((AuditMain.AuditStart) = #" & Me.LastAudit & "#));"
    
    Debug.Print strSQL
    Set rsDate = CurrentDb.OpenRecordset(strSQL)
    
    With rsDate
        If .RecordCount > 0 Then
            .MoveFirst
            Me!ReportDate = !AuditReportDate
            Me!CAE = !CAE
            Me!tempRiskRating = !RiskRating
            Me.RiskRatingOveride = False
        End If
    End With
    
End Sub

[Robeen]

Any chance that you could post your database here?

[Robeen]

This is from your earlier post:

=DAvg("[CAE]","qryBASCMonthlyReport1","(([FacilityTypeID] = 1)) AND ([Reporting]) AND ([ReportDate] = month(dLookup('ReportMonth','ReportDetails'))) AND (year([ReportDate]) = year(dLookup('ReportMonth','ReportDetails')))")

I was looking at how that DAvg function you have in there was constructed and there may be errors in the parts that I have shown in red above.
1.
It feels to me like "AND ([Reporting])" should be changed to something like:
AND ([Reporting] = SomeValue)
or
AND ([Reporting] > SomeValue)
or
AND ([Reporting] <= SomeValue)

2.
I think the single-quotes in your dlookup functions should be double-quotes.
year(dLookup("ReportMonth","ReportDetails"))

Your DAvg() function is basically saying:

DAvg                                            '(Give me the Average of).

"[CAE]"                                         '(The values in the field named "CAE").

"qryBASCMonthlyReport1"                         '(From {I assume} the query "qryBASCMonthlyReport1").

WHERE the following criteria are met:
1. [FacilityTypeID] = 1
AND 
2. ([Reporting]) ??????? This is the criteria I don't get [I think it is missing an Operator {=, >, <, >=, <= . . . } and a Value].
AND 
3. [ReportDate] = month(dLookup('ReportMonth','ReportDetails'))
AND
4. year([ReportDate]) = year(dLookup('ReportMonth','ReportDetails'))

I think that might be where your problem is.
See if changing those makes any difference.