Count Weeks Between Start and End Date

[Intui_Sol]

I would like to display data for the weeks not recorded to make a
graph.

For example I collect the following data:

Start Date Start Date Week End Date End Date Week
4/30/2008 18 5/21/2008 21

I would like the following to display:

Open
Week 18 Week 19 Week 20 Week 21
1 1 1 1
Closed
Week 18 Week 19 Week 20 Week 21
Null Null Null 1

I don't know how I would get Week 19 and Week 20 to show up. Please
help

 

[Golfinray]

I don't know if it will help, but you can use datediff. Try
datediff("w",[start],[end])
The "w" will give you the number of weeks. Use whatever the name of your
start fields and end fields in the square brackets.

 

[John W. Vinson]

I don't know if it will help, but you can use datediff. Try
datediff("w",[start],[end])
The "w" will give you the number of weeks. Use whatever the name of your
start fields and end fields in the square brackets.

I believe it's actually "ww" - "w" gives the weekday and is functionally the
same as "d", which is pretty useless if you ask me!

 

[KARL DEWEY]

This is close to what you want --
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum spread. Use these two queries --
Intui_Sol_1 ---
SELECT Intui_Sol.x, Intui_Sol.RecordDate, DateAdd("ww",[CountNUM],[Enter
Start Date]) AS All_Weeks
FROM Intui_Sol, CountNumber
WHERE (((DateAdd("ww",[CountNUM],[Enter Start Date]))<=CVDate([Enter End
Date])))
GROUP BY Intui_Sol.x, Intui_Sol.RecordDate, DateAdd("ww",[CountNUM],[Enter
Start Date]);

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM First((IIf([RecordDate]=[All_Weeks],"Open","Closed"))) AS CountOfy
SELECT (IIf([RecordDate]=[All_Weeks],"Open","Closed")) AS Status
FROM Intui_Sol_1
GROUP BY (IIf([RecordDate]=[All_Weeks],"Open","Closed"))
ORDER BY (IIf([RecordDate]=[All_Weeks],"Open","Closed")) DESC
PIVOT "Week " & Format([All_Weeks],"ww");