G
Guest
i have two SQL queries filtering data for my report but I keep getting this
error when I run the report. The Microsoft jet database engine does not
reconize" as a valid field name or expression.
This is a Sumation report with two parameters in it to pass the start date
and end date for filtring of the report. The query under the report is as
follows:
PARAMETERS [Forms]![Report Summary]![start date] DateTime, [Forms]![Report
Summary]![end date] DateTime;
TRANSFORM Sum([Sumation Union].Part) AS SumOfPart
SELECT [Sumation Union].Name, Sum([Sumation Union].Part) AS Total
FROM [Sumation Union]
GROUP BY [Sumation Union].Name
PIVOT [Sumation Union].Catagory;
The Sumation Union query is ask follows:
PARAMETERS [Forms]![Report Summary]![start date] DateTime, [Forms]![Report
Summary]![end date] DateTime;
SELECT Employee.EmployeeName AS Name, Count(SafeStart.SafeStartID) AS Part,
Format((SafeStart.SSDate),"mm/dd/yyyy") AS dDate, "Safe Start" AS Catagory
FROM SafeStart INNER JOIN (Employee INNER JOIN SafeStartParticipant ON
Employee.EmployeeId = SafeStartParticipant.EmployeeID) ON
SafeStart.SafeStartID = SafeStartParticipant.SafeStartRecordID
WHERE (((Employee.EmployeeName)<>"")) AND (SafeStart.SSDate BETWEEN
[Forms]![Report Summary]![start date] AND [Forms]![Report Summary]![end
date])
GROUP BY Employee.EmployeeName, SafeStart.SSDate;
Union
SELECT [Well done].Originator AS Name, Count([Well done].Date) AS Part,
Format(([Well done].Date),"mm/dd/yyyy") AS dDate, "Well Done" As Catagory
FROM [Well done]
WHERE ([Well done].Originator <> "") AND ([Well done].Date BETWEEN
[Forms]![Report Summary]![start date] AND [Forms]![Report Summary]![end
date])
GROUP BY [Well done].Originator, ([Well done].Date);
Union
SELECT Observations.Name, Count(Observations.[Observation Number]) AS Part,
Format((Observations.[Observation Date]), "mm/dd/yyyy") AS dDate,
"Observation" As Catagory
FROM Observations
WHERE ((Observations.[Name])<>"") AND (Observations.[Observation Date]
BETWEEN [Forms]![Report Summary]![start date] AND [Forms]![Report
Summary]![end date])
GROUP BY Observations.Name, Observations.[Observation Date];
Union
SELECT Radar.[Created by] AS Name, Count(Radar.[Radar Number]) AS Part,
Format((Radar.Date), "mm/dd/yyyy") AS dDate, "Radar" As Catagory
FROM Radar
WHERE (Radar.[Created by]<>"") AND (Radar.Date BETWEEN [Forms]![Report
Summary]![start date] AND [Forms]![Report Summary]![end date])
GROUP BY Radar.[Created by], Radar.Date;
UNION SELECT [near miss].Name, Count([near miss].[Record number]) AS Part,
Format(([near miss].Date),"mm/dd/yyyy") AS dDate, "Near Miss" As Catagory
FROM [near miss]
WHERE ([near miss].Name <>"") AND ([near miss].Date BETWEEN [Forms]![Report
Summary]![start date] AND [Forms]![Report Summary]![end date])
GROUP BY [near miss].Name, [near miss].Date
ORDER BY Catagory;
I'm a bit baffled on why I keep getting this error. The form that calls this
report has two date fields on it and the report should be pointing to those
fields.
Any help would be appreciated.
error when I run the report. The Microsoft jet database engine does not
reconize" as a valid field name or expression.
This is a Sumation report with two parameters in it to pass the start date
and end date for filtring of the report. The query under the report is as
follows:
PARAMETERS [Forms]![Report Summary]![start date] DateTime, [Forms]![Report
Summary]![end date] DateTime;
TRANSFORM Sum([Sumation Union].Part) AS SumOfPart
SELECT [Sumation Union].Name, Sum([Sumation Union].Part) AS Total
FROM [Sumation Union]
GROUP BY [Sumation Union].Name
PIVOT [Sumation Union].Catagory;
The Sumation Union query is ask follows:
PARAMETERS [Forms]![Report Summary]![start date] DateTime, [Forms]![Report
Summary]![end date] DateTime;
SELECT Employee.EmployeeName AS Name, Count(SafeStart.SafeStartID) AS Part,
Format((SafeStart.SSDate),"mm/dd/yyyy") AS dDate, "Safe Start" AS Catagory
FROM SafeStart INNER JOIN (Employee INNER JOIN SafeStartParticipant ON
Employee.EmployeeId = SafeStartParticipant.EmployeeID) ON
SafeStart.SafeStartID = SafeStartParticipant.SafeStartRecordID
WHERE (((Employee.EmployeeName)<>"")) AND (SafeStart.SSDate BETWEEN
[Forms]![Report Summary]![start date] AND [Forms]![Report Summary]![end
date])
GROUP BY Employee.EmployeeName, SafeStart.SSDate;
Union
SELECT [Well done].Originator AS Name, Count([Well done].Date) AS Part,
Format(([Well done].Date),"mm/dd/yyyy") AS dDate, "Well Done" As Catagory
FROM [Well done]
WHERE ([Well done].Originator <> "") AND ([Well done].Date BETWEEN
[Forms]![Report Summary]![start date] AND [Forms]![Report Summary]![end
date])
GROUP BY [Well done].Originator, ([Well done].Date);
Union
SELECT Observations.Name, Count(Observations.[Observation Number]) AS Part,
Format((Observations.[Observation Date]), "mm/dd/yyyy") AS dDate,
"Observation" As Catagory
FROM Observations
WHERE ((Observations.[Name])<>"") AND (Observations.[Observation Date]
BETWEEN [Forms]![Report Summary]![start date] AND [Forms]![Report
Summary]![end date])
GROUP BY Observations.Name, Observations.[Observation Date];
Union
SELECT Radar.[Created by] AS Name, Count(Radar.[Radar Number]) AS Part,
Format((Radar.Date), "mm/dd/yyyy") AS dDate, "Radar" As Catagory
FROM Radar
WHERE (Radar.[Created by]<>"") AND (Radar.Date BETWEEN [Forms]![Report
Summary]![start date] AND [Forms]![Report Summary]![end date])
GROUP BY Radar.[Created by], Radar.Date;
UNION SELECT [near miss].Name, Count([near miss].[Record number]) AS Part,
Format(([near miss].Date),"mm/dd/yyyy") AS dDate, "Near Miss" As Catagory
FROM [near miss]
WHERE ([near miss].Name <>"") AND ([near miss].Date BETWEEN [Forms]![Report
Summary]![start date] AND [Forms]![Report Summary]![end date])
GROUP BY [near miss].Name, [near miss].Date
ORDER BY Catagory;
I'm a bit baffled on why I keep getting this error. The form that calls this
report has two date fields on it and the report should be pointing to those
fields.
Any help would be appreciated.