Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 30th, 2005, 10:12 AM
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default Veiw Reports only for current month.

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
Reply With Quote
  #2 (permalink)  
Old September 30th, 2005, 10:48 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Dharmesh,

Check the system date and compare the report data month.

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

Hope this helps.

Kevin




dartcoach
Reply With Quote
  #3 (permalink)  
Old September 30th, 2005, 11:11 AM
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default

So where do I put that code?

Dharmesh Bhavsar
Reply With Quote
  #4 (permalink)  
Old September 30th, 2005, 11:13 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old September 30th, 2005, 11:19 AM
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default

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
Reply With Quote
  #6 (permalink)  
Old September 30th, 2005, 11:22 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Kevin

dartcoach
Reply With Quote
  #7 (permalink)  
Old September 30th, 2005, 11:27 AM
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default

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
Reply With Quote
  #8 (permalink)  
Old September 30th, 2005, 11:32 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Dharmesh,

I need to see the sql for Extrusion Report Query

Also, what is in the table Calendar?

Kevin

dartcoach
Reply With Quote
  #9 (permalink)  
Old September 30th, 2005, 11:35 AM
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default

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
Reply With Quote
  #10 (permalink)  
Old September 30th, 2005, 11:41 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting month name to month number kalyanykk SQL Server 2005 7 August 19th, 2008 10:37 PM
month calendar bschleusner C# 2005 0 April 13th, 2007 09:46 PM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
get current month cathiec SQL Language 2 February 23rd, 2006 09:58 AM
Getting the current month Regornil JSP Basics 1 July 19th, 2004 09:01 PM



All times are GMT -4. The time now is 04:24 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.