Runtime error 3070

[AmanKaur123]

Hi Guys



I have written a crosstab query which is ;linked to a report . The query in itself runs fine without any issues but when I try to run the report then following error comes up:
"Microsoft Access database engine doesn't recognize " as a valid field name or expression"

I have defined the parameters in the query as well but this error is really annoying me. Please see below my query:

PARAMETERS Forms!frmreport!txtDateStart DateTime, Forms!frmreport!txtDateEnd DateTime;
TRANSFORM Count(qryCallAudits_MCMA.AuditID) AS CountOfAuditID
SELECT qryCallAudits_MCMA.[Staff Name], Count(qryCallAudits_MCMA.AuditID) AS [Total Of AuditID]
FROM qryCallAudits_MCMA
WHERE (((qryCallAudits_MCMA.[Date of Observation])>=[forms]![frmreport]![txtDateStart] And (qryCallAudits_MCMA.[Date of Observation])<=[forms]![frmReport]![txtDateEnd]))
GROUP BY qryCallAudits_MCMA.[Staff Name]
PIVOT qryCallAudits_MCMA.OutcomeID;

Any help will be much appreciated. Thanks.

[ranman256]

reports on crosstabs can fail due to missing fields.
Does the query run correctly alone as a query?

IF the query runs fine, then you must build a report that has EVERY possible outcome the query can produce.
I suggest a 'report table' that has all these fields, append crosstab data to it THEN report on this table.

[AmanKaur123]

Ranman, The query runs perfectly on its own. Please see below the query which runs fine but as soon as I add AuditorID field in the query then it starts giving hat error message:
Query without AuditorID field:

PARAMETERS [Forms]![frmreport]![txtDateStart] DateTime, [Forms]![frmreport]![txtDateEnd] DateTime, [Forms]![frmreport]![lststaff] Long;
TRANSFORM Count(qryCallAuditReport_New.AuditID) AS CountOfAuditID
SELECT qryCallAuditReport_New.[Staff Name], qryCallAuditReport_New.StandardType, Count(qryCallAuditReport_New.AuditID) AS [Total Of AuditID]
FROM qryCallAuditReport_New
WHERE (((qryCallAuditReport_New.[Date of Observation])>=[forms]![frmreport]![txtDateStart] And (qryCallAuditReport_New.[Date of Observation])<=[forms]![frmReport]![txtDateEnd]))
GROUP BY qryCallAuditReport_New.[Staff Name], qryCallAuditReport_New.StandardType
PIVOT qryCallAuditReport_New.Score;

Query with Auditor ID field:

PARAMETERS [Forms]![frmreport]![txtDateStart] DateTime, [Forms]![frmreport]![txtDateEnd] DateTime, [Forms]![frmreport]![lststaff] Long;
TRANSFORM Count(qryCallAuditReport_New.AuditID) AS CountOfAuditID
SELECT qryCallAuditReport_New.[Staff Name], qryCallAuditReport_New.StandardType, Count(qryCallAuditReport_New.AuditID) AS [Total Of AuditID]
FROM qryCallAuditReport_New
WHERE (((qryCallAuditReport_New.[Date of Observation])>=[forms]![frmreport]![txtDateStart] And (qryCallAuditReport_New.[Date of Observation])<=[forms]![frmReport]![txtDateEnd]) AND ((qryCallAuditReport_New.AuditorID)=[forms]![frmReport]![lstStaff]))
GROUP BY qryCallAuditReport_New.[Staff Name], qryCallAuditReport_New.StandardType
PIVOT qryCallAuditReport_New.Score;

[aytee111]

runs fine but as soon as I add AuditorID field in the query then it starts giving hat error message

That is your problem then, isn't it? Do as Ranman says - make a report table from the query and use that for your report.

[AmanKaur123]

reports on crosstabs can fail due to missing fields.

I suggest a 'report table' that has all these fields, append crosstab data to it THEN report on this table.

Hi Ranman, Could you please help me how to get this done? I have never done it before. Please see below the query. It runs fine for few Staff members when the Score field gives all the result i.e (0,1,2,3)

But for the Staff member whose scoring suppose doesn't include 1 , it throws the error message.

If any of the score is missing the error comes up. Any help will be much appreciated.

PARAMETERS [forms]![frmreport]![lstStaff] Long, [forms]![frmreport]![txtDateStart] DateTime, [forms]![frmreport]![txtDateEnd] DateTime;
TRANSFORM Count(qryCallAudit_Staff.AuditID) AS CountOfAuditID
SELECT qryCallAudit_Staff.StandardType, qryCallAudit_Staff.[Call Type], qryCallAudit_Staff.SignOffType
FROM qryCallAudit_Staff
WHERE (((qryCallAudit_Staff.[Date of Observation])>=[forms]![frmreport]![txtDateStart] And (qryCallAudit_Staff.[Date of Observation])<=[forms]![frmreport]![txtDateEnd]) AND ((qryCallAudit_Staff.[Staff Number])=[forms]![frmreport]![lstStaff]))
GROUP BY qryCallAudit_Staff.StandardType, qryCallAudit_Staff.[Call Type], qryCallAudit_Staff.SignOffType, qryCallAudit_Staff.[Date of Observation], qryCallAudit_Staff.[Staff Number]
PIVOT qryCallAudit_Staff.Score;