Displaying Week Ending "Regan via AccessMonster.com" <u24090[ at ]uwe> 21.07.2006 03:14:38 Hi, This one has been bugging me This is my query SELECT DISTINCTROW Format$(TimeSheet.Date,'ww yyyy',4) AS [Week Ending], TimeSheet.Employee, Sum(TimeSheet.Units) AS [Sum Of Units], Count(*) AS [Count Of TimeSheet] FROM Employees INNER JOIN TimeSheet ON Employees.EmployeeID = TimeSheet. Employee WHERE (((TimeSheet.Date)>=[forms]![Startup]![1sttsent] And (TimeSheet.Date)<= [Forms]![startup]![wkedng]) AND ((([TimeSheet].[Code]))<>24)) GROUP BY Format$(TimeSheet.Date,'ww yyyy',4), TimeSheet.Employee; It show the info i want. Except for the Column (Week Ending), it displays the following Week Ending 23 2006 24 2006 etc Thou i want it to Display Week Ending 04/06/2006 11/06/2006 18/06/2006 i have this query in a form. Now i don't care if it is displayed ( 23 2006) in query. But i want to display it as ( 11/06/2006 ) in the form. Can anyone help me with this? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1

Re: Displaying Week Ending "John Spencer" <spencer[ at ]chpdm.edu> 21.07.2006 12:00:28 Haven't tested this fully, but you might try something like the following to calculate the week ending date. If it doesn't work, at least it may give you some ideas on how to do this. DateAdd("d",4-Weekday(TimeSheet.Date,4),TimeSheet.Date)+ 6 "Regan via AccessMonster.com" <u24090[ at ]uwe> wrote in message news:638edff0c4df2[ at ]uwe... [Quoted Text] > Hi, This one has been bugging me > This is my query > > SELECT DISTINCTROW Format$(TimeSheet.Date,'ww yyyy',4) AS [Week Ending], > TimeSheet.Employee, Sum(TimeSheet.Units) AS [Sum Of Units], Count(*) AS > [Count Of TimeSheet] > FROM Employees INNER JOIN TimeSheet ON Employees.EmployeeID = TimeSheet. > Employee > WHERE (((TimeSheet.Date)>=[forms]![Startup]![1sttsent] And > (TimeSheet.Date)<= > [Forms]![startup]![wkedng]) AND ((([TimeSheet].[Code]))<>24)) > GROUP BY Format$(TimeSheet.Date,'ww yyyy',4), TimeSheet.Employee; > > It show the info i want. > Except for the Column (Week Ending), it displays the following > > Week Ending > 23 2006 > 24 2006 etc > > Thou i want it to Display > > Week Ending > 04/06/2006 > 11/06/2006 > 18/06/2006 > > i have this query in a form. Now i don't care if it is displayed ( 23 > 2006) > in query. But i want to display it as ( 11/06/2006 ) in the form. Can > anyone help me with this? > > Thanks > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1

Re: Displaying Week Ending "Regan via AccessMonster.com" <u24090[ at ]uwe> 22.07.2006 06:50:18 ThanksJohn I did end up getting the stupid magic box doing what i wanted. and have learnt alot about Access in last couple of weeks. what an awesome program to make things easier. i'll most probably be jumping on here again next week asking more questions. Thanks again [Quoted Text] >Haven't tested this fully, but you might try something like the following to >calculate the week ending date. If it doesn't work, at least it may give >you some ideas on how to do this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200607/1