Small Query Question- Datepart function

[devagupt]

Hello ,

I have a query which adds all the downtime for a given week and displays it. All i have to do is , is to type the week number and it shows be the data. However we have rolled into the new yr ( 2008). I would like it to display information for 2nd week in Jan 2008 for me. When i enter 2( 2nd week), it displays the 2nd week in 2007.

Please help.

Thanks in advance

Present CODE***
Expr1: DatePart("ww",[tblDowntimeTable1]![Date01])

 

[dhookom]

Please share your entire SQL view. If you are using a parameter prompt query, take a look at the FAQs in the queries forum to read why I hate them.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[devagupt]

Below is the SQL CODE. if you look at the last line , it has the datepart function in it.
Thanks


CODE*****


SELECT DatePart("ww",[tblDowntimeTable1]![Date01]) AS Expr1, tblDowntimeTable1.Line, Sum([tblDowntimeTable1]![CH/LD Change]*0.0167) AS [CH/LDchange], Sum([tblDowntimeTable1]![Start up]*0.0167) AS Startup, Sum([tblDowntimeTable1]![FIne Tuning]*0.0167) AS [Fine Tuning], Sum([tblDowntimeTable1]![Making Boxes]*0.0167) AS [Making Boxes], Sum([tblDowntimeTable1]![Morning Meet]*0.0167) AS [Morning Meet], Sum([tblDowntimeTable1]![Product Change]*0.0167) AS [Product Change], Sum([tblDowntimeTable1]![Roll count]*0.0167) AS Rollcount, Sum([tblDowntimeTable1]![Rotation]*0.0167) AS Rotation1, Sum([tblDowntimeTable1]![Comp Control]*0.0167) AS CompControl, Sum([tblDowntimeTable1]![Bad components]*0.0167) AS Badcomponents, Sum([tblDowntimeTable1]![Re-ins/Re-work]*0.0167) AS [Re-ins\Re-work], Sum([tblDowntimeTable1]![Miscellaneous]*0.0167) AS Miscellaneous, Sum([tblDowntimeTable1]![Label Machine]*0.0167) AS LabelMachine, Sum([tblDowntimeTable1]![Dabrico]*0.0167) AS Dabrico, Sum([tblDowntimeTable1]![Eisai]*0.0167) AS Eisai, Sum([tblDowntimeTable1]![Siednader]*0.0167) AS Siednader, Sum([tblDowntimeTable1]![Dividella]*0.0167) AS Dividella, Sum([tblDowntimeTable1]![Marchesini]*0.0167) AS Marchesini, Sum([tblDowntimeTable1]![Klockner]*0.0167) AS Klockner, Sum([tblDowntimeTable1]![Miscellaneous Equipment]*0.0167) AS [Miscellaneous Equipment], Sum(([tblDowntimeTable1]![Dividella]+[tblDowntimeTable1]![CH/LD Change]+[tblDowntimeTable1]![Klockner]+[tblDowntimeTable1]![Label Machine]+[tblDowntimeTable1]![Product Change]+[tblDowntimeTable1]![Roll count]+[tblDowntimeTable1]![Rotation]+[tblDowntimeTable1]![Comp Control]+[tblDowntimeTable1]!
[Start up]+[tblDowntimeTable1]![Marchesini]+[tblDowntimeTable1]![Eisai]+[tblDowntimeTable1]![Bad components]+[tblDowntimeTable1]![Re-ins/Re-work]+[tblDowntimeTable1]![Dabrico]+[tblDowntimeTable1]![Miscellaneous Equipment]+[tblDowntimeTable1]![Siednader]+[tblDowntimeTable1]![Fine Tuning]+[tblDowntimeTable1]![Morning Meet]+[tblDowntimeTable1]![Making Boxes]+[tblDowntimeTable1]![Miscellaneous])*0.0167) AS Total
FROM tblDowntimeTable1
GROUP BY DatePart("ww",[tblDowntimeTable1]![Date01]), tblDowntimeTable1.Line
HAVING (((DatePart("ww",[tblDowntimeTable1]![Date01]))=50))
ORDER BY DatePart("ww",[tblDowntimeTable1]![Date01]);

 

[dhookom]

Create a form with 2 combo boxes for your users to select Year and Week values. Then change your query to

SELECT tblDowntimeTable1.Line, Sum([tblDowntimeTable1]![CH/LD Change]*0.0167) AS [CH/LDchange], Sum([tblDowntimeTable1]![Start up]*0.0167) AS Startup, Sum([tblDowntimeTable1]![FIne Tuning]*0.0167) AS [Fine Tuning], Sum([tblDowntimeTable1]![Making Boxes]*0.0167) AS [Making Boxes], Sum([tblDowntimeTable1]![Morning Meet]*0.0167) AS [Morning Meet], Sum([tblDowntimeTable1]![Product Change]*0.0167) AS [Product Change], Sum([tblDowntimeTable1]![Roll count]*0.0167) AS Rollcount, Sum([tblDowntimeTable1]![Rotation]*0.0167) AS Rotation1, Sum([tblDowntimeTable1]![Comp Control]*0.0167) AS CompControl, Sum([tblDowntimeTable1]![Bad components]*0.0167) AS Badcomponents, Sum([tblDowntimeTable1]![Re-ins/Re-work]*0.0167) AS [Re-ins\Re-work], Sum([tblDowntimeTable1]![Miscellaneous]*0.0167) AS Miscellaneous, Sum([tblDowntimeTable1]![Label Machine]*0.0167) AS LabelMachine, Sum([tblDowntimeTable1]![Dabrico]*0.0167) AS Dabrico, Sum([tblDowntimeTable1]![Eisai]*0.0167) AS Eisai, Sum([tblDowntimeTable1]![Siednader]*0.0167) AS Siednader, Sum([tblDowntimeTable1]![Dividella]*0.0167) AS Dividella, Sum([tblDowntimeTable1]![Marchesini]*0.0167) AS Marchesini, Sum([tblDowntimeTable1]![Klockner]*0.0167) AS Klockner, Sum([tblDowntimeTable1]![Miscellaneous Equipment]*0.0167) AS [Miscellaneous Equipment], Sum(([tblDowntimeTable1]![Dividella]+[tblDowntimeTable1]![CH/LD Change]+[tblDowntimeTable1]![Klockner]+[tblDowntimeTable1]![Label Machine]+[tblDowntimeTable1]![Product Change]+[tblDowntimeTable1]![Roll count]+[tblDowntimeTable1]![Rotation]+[tblDowntimeTable1]![Comp Control]+[tblDowntimeTable1]!
[Start up]+[tblDowntimeTable1]![Marchesini]+[tblDowntimeTable1]![Eisai]+[tblDowntimeTable1]![Bad components]+[tblDowntimeTable1]![Re-ins/Re-work]+[tblDowntimeTable1]![Dabrico]+[tblDowntimeTable1]![Miscellaneous Equipment]+[tblDowntimeTable1]![Siednader]+[tblDowntimeTable1]![Fine Tuning]+[tblDowntimeTable1]![Morning Meet]+[tblDowntimeTable1]![Making Boxes]+[tblDowntimeTable1]![Miscellaneous])*0.0167) AS Total
FROM tblDowntimeTable1
WHERE Year(Date01) = Forms!frmSelectYrMth!cboYear AND 
DatePart("ww",[Date01])= Forms!frmSelectYrMth!cboWeek
GROUP BY tblDowntimeTable1.Line;

If this is your actual table structure, I think you have normalization issues. It looks like your field names store data values.

Duane MS Access MVP
Now help me support United Cerebral Palsy