Parameter Query: User inputs week

[aamudog]

Hello all. I am really new to access. I am trying to create a database to keep track of the hours I work on projects each day and then create a report which shows the hours worked on each project by the week. The week starts on Monday and ends on Sunday. I would like to be able to prompt the user to enter the date for the end of the week and return the hours for each project for that week. So if the user enters 12/10/06 the report would display the hours for 12/4 - 12/10. I had a crosstab query setup so that I could enter the week number and have it return the hours for each project but then I realized that if I have more than a year of data that i would get too much data returned. Can someone help me edit my query to get what I need? Here's what I have now.


PARAMETERS [type your day here] Integer;
TRANSFORM Sum(tblProjectHours.BillableHours) AS SumOfBillableHours
SELECT tblProjectHours.ProjectID, tblProjectHours.BillingDate, Sum(tblProjectHours.BillableHours) AS [Total Of BillableHours]
FROM tblProjectHours
WHERE DatePart("ww",[BillingDate])= [type your day here]
GROUP BY tblProjectHours.ProjectID, tblProjectHours.BillingDate
PIVOT (Format([BillingDate],'dddd'));

 

[Shawn12]

Did you try putting the [type your day here] into the query(ies) that you have in the x-tab rather than putting the entry in the x-tab itself?

 

[Shawn12]

NM I see you are using tables not queries.

 

[aamudog]

Thanks all. I got it working. Here's what I did in case someone needs it.


PARAMETERS [type your date here] DateTime;
TRANSFORM Sum(tblProjectHours.BillableHours) AS SumOfBillableHours
SELECT tblProjectHours.ProjectID, tblProjectHours.BillingDate, Sum(tblProjectHours.BillableHours) AS [Total Of BillableHours]
FROM tblProjectHours
WHERE (((tblProjectHours.BillingDate) Between [type your date here]-6 And [type your date here]))
GROUP BY tblProjectHours.ProjectID, tblProjectHours.BillingDate
PIVOT (Format([BillingDate],'dddd'));