I have set parameters to view report by asking the user to enter Month name they would like to view reports for. Is there a way to view reports for current month whithout parameters?

Dharmesh Bhavsar

Dharmesh,

Check the system date and compare the report data month.

DatePart("m", Date()) = DatePart("m", ReportDate)

Hope this helps.

Kevin




dartcoach

So where do I put that code?

Dharmesh Bhavsar

Dharmesh,
Where do you check for their input month? You need to replace the code that compares their input month with the above code.

Kevin

dartcoach

Well soon as they click view a certain report, I want the access to automatically show the report for the current month without promting them to enter Month name.

Dharmesh Bhavsar

What is the report source? A table or a query? If it's a query, can you send me the sql?

Kevin

dartcoach

its too long but here you go: SELECT DISTINCTROW [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], (([Total Hours]-[Sum Of Total Down time_Total Hours])/[Total Hours]) AS Utilization, [Total LBS Made]/[Production Total Hours] AS [Total LBS/Hr], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], IIf(IsNull([Sum Of Production D/T Hours]/[Total Hours]),0,[Sum Of Production D/T Hours]/[Total Hours]) AS [Production D/T], IIf(IsNull([Sum Of Maintenance hours]/[Total Hours]),0,[Sum Of Maintenance hours]/[Total Hours]) AS [Maintenance D/T], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], Sum((IIf(IsNull([Maintenance hours]),0,[Maintenance hours]))) AS [Sum Of Maintenance hours], Sum((IIf(IsNull([Production D/T Hours]),0,[Production D/T Hours]))) AS [Sum Of Production D/T Hours], Sum(Nz([Total Down time]![Total Hours],0)) AS [Sum Of Total Down time_Total Hours], [Production Hours Total].[Production Total Hours], Nz([Production Total Hours],0)+Nz([Sum Of Total Down time_Total Hours],0) AS [Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO
FROM ([Production Hours Total] INNER JOIN ([Daily Production Summary Totals] RIGHT JOIN ((([Extrusion Report Query] LEFT JOIN Maintenance ON ([Extrusion Report Query].DownTimeList = Maintenance.DownTimeList) AND ([Extrusion Report Query].[Operator Id] = Maintenance.[Operator Id]) AND ([Extrusion Report Query].[Shift#] = Maintenance.[Shift#]) AND ([Extrusion Report Query].[Line#] = Maintenance.[Line#]) AND ([Extrusion Report Query].Date = Maintenance.Date) AND ([Extrusion Report Query].[Total Hours] = Maintenance.[Maintenance hours])) LEFT JOIN [Production Downtime] ON ([Extrusion Report Query].[Shift#] = [Production Downtime].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Production Downtime].[Line#]) AND ([Extrusion Report Query].Date = [Production Downtime].Date) AND ([Extrusion Report Query].[Total Hours] = [Production Downtime].[Production D/T Hours]) AND ([Extrusion Report Query].DownTimeList = [Production Downtime].DownTimeList) AND ([Extrusion Report Query].[Operator Id] = [Production Downtime].[Operator Id])) LEFT JOIN [Total Down time] ON ([Extrusion Report Query].[Shift#] = [Total Down time].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Total Down time].[Line#]) AND ([Extrusion Report Query].Date = [Total Down time].Date) AND ([Extrusion Report Query].[Total Hours] = [Total Down time].[Total Hours]) AND ([Extrusion Report Query].[Operator Id] = [Total Down time].[Operator Id]) AND ([Extrusion Report Query].DownTimeList = [Total Down time].DownTimeList)) ON ([Daily Production Summary Totals].Date = [Extrusion Report Query].Date) AND ([Daily Production Summary Totals].[Line#] = [Extrusion Report Query].[Line#]) AND ([Daily Production Summary Totals].[Shift#] = [Extrusion Report Query].[Shift#])) ON ([Production Hours Total].[Shift#] = [Extrusion Report Query].[Shift#]) AND ([Production Hours Total].[Line#] = [Extrusion Report Query].[Line#]) AND ([Production Hours Total].Date = [Extrusion Report Query].Date)) INNER JOIN Calendar ON [Production Hours Total].Date = Calendar.Date
GROUP BY [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], [Production Hours Total].[Production Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO;


Dharmesh Bhavsar

Dharmesh,

I need to see the sql for Extrusion Report Query

Also, what is in the table Calendar?

Kevin

dartcoach

The Calender table is the calender company follows for production reports and here is the sql for exrusion 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
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
HAVING (((Calendar.Month)=[Enter Month Name]))
ORDER BY [Extrusion Report].Date, [Extrusion Report].[Line#], [Extrusion Report].[Shift#], [Extrusion Report].DownTimeList;


Dharmesh Bhavsar

Dharmesh,
In the table Calendar, what is the format of Month? Is it the full name of the month, i.e. January, or Jan?

Kevin

dartcoach