Parameter Query

[Groundrush]

Is this possible?

I have a parameter qry that returns data within a start and end date, which is handy if I am looking for a particular week which will consist of 39 hours

the problem is when I use it to run a monthly report I cant get it to break it down to week ending dates

please see attached for example

this is the SQL I am using

SELECT dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC, Sum(dbo_F_TASK_TIME.TT_NORM_TIME) AS SumOfTT_NORM_TIME, dbo_F_TASK_TIME.TT_STARTED
FROM dbo_F_TASK_TIME INNER JOIN dbo_F_TASKS ON dbo_F_TASK_TIME.TT_FKEY_TA_SEQ = dbo_F_TASKS.TA_SEQ
WHERE (((dbo_F_TASK_TIME.TT_STARTED)>=[Start Date] And (dbo_F_TASK_TIME.TT_STARTED)<[end date plus 1 day]))
GROUP BY dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC, dbo_F_TASK_TIME.TT_STARTED
HAVING (((Sum(dbo_F_TASK_TIME.TT_NORM_TIME))<>0))
ORDER BY dbo_F_TASK_TIME.TT_NAME;


Thanks

 

[neileg]

You need something to identify which week the days fall into. There's nothing in your query that does this, all the data is identified by day. I would add a caculated field that works out what the week number is and you can then group by week number.

 

[Groundrush]

Thanks

I have tried that before but could not find the best way of doing so

Something like:

01-04-03 =week1
02-04-03 =week1
03-04-03 =week1
04-04-03 =week1
05-04-03 =week1
06-04-03 =week1
07-04-03 =week2
08-04-03 =week2..........and so on fo the whole year

I shall have onother attempt

Cheers

 

[neileg]

Use DateDiff to calculate the number of weeks since 1 April. Look in Access help for this. You might have to add 1 to get the week number.

 

[uncle-lai]

Create a new field in your query:

WeekNo: Format([YourDate], "ww", 2)

This will return the week number (in the year) with Monday as the first day of the week.

Peter

 

[Rich]

What's the start date of your Financial year?

 

[Groundrush]

Rich.....

The 1st April is the start of our Financial year.

 

[neileg]

That's why I suggested DateDiff based on 1 April

 

[Rich]

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 4, 1), 1, 0)
End Function


Public Function FinancialWeek(dDate As Date) As Integer
FinancialWeek = (dDate - DateSerial(FinancialYear(dDate), 4, 1) + 7) \ 7
End Function

 

[Groundrush]

Thanks for the replies everyone!

Rich, your advice looks interesting, how do I use it?

so far I have created a table with week No's and to what day it refers to with the idea of linking it to my main qry.


but I can't work out how to use your solution

cheers

 

[Rich]

Add two fields to your query, FiscalYear:FinancialYear([YourDateField]) , FiscalWeek:FinancialWeek([[YourDateField])

 

[Groundrush]

Thanks Rich
I understand the bit about adding the two fields to my qry
but not to sure about the Public Functions you provided earlier

where do I put those?

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 4, 1), 1, 0)
End Function


Public Function FinancialWeek(dDate As Date) As Integer
FinancialWeek = (dDate - DateSerial(FinancialYear(dDate), 4, 1) + 7) \ 7
End Function

Thanks again

 

[Rich]

Into a new module, call the module basFiscal or some meaningful name, remember to compile and save it

 

[Groundrush]

Thanks Rich

Thanks Rich

now I get it, It did cross my mind to put it into a module

but what got me was that I had never heard of a public function before and I could not understand how it would work with my qry

Will give it a go Monday Morning

Thanks for your time