This is what I have currently for the table data. I have to filter the table
data with a pivot query but this query below is what is feeding the data to
the pivot table. I need to have the form pass the StartDate and EndDate to
the Union query.
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
[StartDate] AND [EndDate])
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
[StartDate] AND [EndDate])
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 [StartDate] AND [EndDate])
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 [StartDate] AND
[EndDate])
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 [StartDate]
AND [EndDate])
GROUP BY [near miss].Name, [near miss].Date
ORDER BY Catagory;
The pivot table is using the above query as data and the SQL for the pivot
is as follows:
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;
I keep getting this error saying that the Jet doesn't understand the link to
the form. How do I get around that?
Ofer Cohen said:
If the text boxes in the form, then you can refer to this text boxes from the
query
Select * From TableName Where DateFieldName Between
Forms![FormName]![StartDateTextBox] And Forms![FormName]![EndDateTextBox]
In the Report you can create a text box that display this value using the
same path
=" The date are between " & Forms![FormName]![StartDateTextBox] & " And " &
Forms![FormName]![EndDateTextBox]
Mybe that what you are asking for
--
Good Luck
BS"D
Cameron said:
I have a SQL query that builds the data for a report, but I'm kind of stuck
at how to transfer the parameters data from the form to the SQL script to get
the correct data to be presented in the report.
I have two date text boxes that the user enders date and then that data
should be passed to the query and then in turn that data should be presented
on the report.
