KAnoe said:
I would like to a the user type in a date. Say 3 Jan 05 and the Q would give
all the records for that week. So I would get records from 3 jan - 9 jan 05.
I put in the Q format ww and that did not work. Any help would be
great.
Here is the first thing off the top of my head:
You have this example table:
CREATE TABLE CalendarWeeks
(WeekStart DATETIME NOT NULL
,WeekEnd DATETIME NOT NULL
,WeekNumber BYTE NOT NULL
,CONSTRAINT pk_CalendarWeeks PRIMARY KEY (WeekStart, WeekEnd)
)
Use the above DDL-SQL to create the table.
Sample Data for CalendarWeeks:
WeekStart WeekEnd WeekNumber
01/01/05 01/07/05 1
..
..
..
12/24/05 12/30/05 52
12/31/05 12/31/05 53
Then, run the following VBA code to load the table:
Public Sub FillCalendarWeeks()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim WeekStart As Date
Dim WeekEnd As Date
Dim LoopDate As Date
Dim YearEndDate As Date
Set db = CurrentDb()
Set rs = db.OpenRecordset("CalendarWeeks")
LoopDate = "01/01/05"
YearEndDate = "01/01/06"
Do While LoopDate <= YearEndDate
With rs
.AddNew
.Fields("WeekStart") = LoopDate
.Fields("WeekEnd") = IIf(LoopDate = "12/31/05", LoopDate,
DateAdd("d", 6, LoopDate))
.Fields("WeekNumber") = DatePart("ww", LoopDate)
.Update
LoopDate = DateAdd("ww", 1, LoopDate)
End With
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Then, run this example Query (Note, it is Parameterized, instead of
using a Forms!etc. format becasue this I'm executing it manually, you
need to replace the parameter with an appropriate control reference):
PARAMETERS MyDate DATETIME;
SELECT CW1.*
FROM CalendarWeeks AS CW1
WHERE MyDate BETWEEN CW1.WeekStart AND CW1.WeekEnd
This query pops out the starting and ending dates of any particular
"calendar week" based on the random date entered by the user, which is
what I believe you were asking for.
(It even has the weeknumber on
hand, so you don't have to process dynamic DatePart calculations.)
Modify the VBA code above appropriately to create a sufficiently large
table (watch the year-end dates)
