With the new sql code posted above, I am able to see all record if I don't enter anything for Month name. you think this might help?

Dharmesh Bhavsar

Dharvesh,

do you still have the parameter line in the sql? If so, take it out, and use the last set of sql.

Kevin

dartcoach

Nevermind, that parameter thing doesn't work. when I don't enter month it shows all the record but when put a month name, it still shows all the records.

Dharmesh Bhavsar

Dharmesh,

Did this sql have the parameter line above it?


SELECT [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, ([Minutes]/60) AS [Total Hours], [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Date, Calendar.Month
FROM [Extrusion DT Log] INNER JOIN (Calendar INNER JOIN [Extrusion Report] ON Calendar.Date = [Extrusion Report].Date) ON [Extrusion DT Log].[Extrusion DT Id] = [Extrusion Report].DownTimeList
GROUP BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month
WHERE (((Datepart("m",Calendar.Date)) = Datepart("m",Date())))
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;


Kevin

dartcoach

that was after I changed it. what I did was I had parameter under the month field, but than I deleted that and clicked on query tab and there is parameters listed on the bottom. Thats where I typed in month as a parameter and datatype text.

Dharmesh Bhavsar

Dharmesh,

Can you copy these tables into a separate mdb and send it to me?

Extrusion DT Log
Calendar
Extrusion Report

If you can, I can play with the sql to see if I can get it to work.

Kevin


dartcoach

send me ur email address so I can send you the database.
you can e-mail me at [email protected]


Dharmesh Bhavsar

Dharmesh,

Heres the new sql for the Extrusion Report Query:

SELECT [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, ([Minutes]/60) AS [Total Hours], [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month, DatePart("m",[Calendar].[Date]) AS Expr1
FROM [Extrusion DT Log] INNER JOIN (Calendar INNER JOIN [Extrusion Report] ON Calendar.Date = [Extrusion Report].Date) ON [Extrusion DT Log].[Extrusion DT Id] = [Extrusion Report].DownTimeList
GROUP BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].[Operator Id], [Extrusion Report].DownTimeList, [Extrusion Report].Minutes, [Extrusion DT Log].[Category 1], [Extrusion DT Log].[Category 2], Calendar.Month, DatePart("m",[Calendar].[Date])
HAVING (((DatePart("m",[Calendar].[Date]))=DatePart("m",Date())))
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;


Paste this in.

Kevin

dartcoach

That actually works but it's not refering to calender month.

Dharmesh Bhavsar

What do you mean?

Kevin

dartcoach