Months to weeks (1 Viewer)

[jesseb]

I am trying to figure out a way to take a month and convert it to a certain amount of weeks. For example, a month starts on 12/31/03. I want to to add 5 weeks and show each week added in a column. So it would look like 12/31/03; 1/7/03; 1/14/03... Does anyone know how to get this accomplished?

 

[TessB]

to calculate 5 weeks from a given date:

=DateAdd("ww", 5, [intial date field name here])

Tess

 

[jesseb]

That is almost what I am looking for. I think I will have to use the dateadd() but the thing is, I need to display the weeks in between the start week and the end week. I am looking to do it in Excel and use some VBA. But if there is a better way to do it in Access that would be cool.

[This message has been edited by jesseb (edited 05-30-2002).]

 

[TessB]

So, wouldn't you just make 5 different columns and code the source of each one:

=DateAdd("ww", 1, [initial date])
=DateAdd("ww", 2, [initial date])

etc.... all the way up to 5?
Am I missing something?

 

[jesseb]

That is what I am going to have to do and would work. But I am going to need to do this every month and the number of weeks to change is always changing. So I would have to put a lot of effort to do it that way every time I needed to do this conversion. This situation could be handled by some for loops gracefully but I only know how to do that in Excel. Thanks for your help!

 

[Rich]

Sub FillTbl()
Dim DB As Database
Dim rst As Recordset
Dim StartDte As Date
Dim I As Integer, Period As Integer
StartDte = Me.dteStart
Period = Me.txtPrd
For I = 0 To Period - 1
Set DB = CurrentDb
Set rst = DB.OpenRecordset("tblDates")
rst.MoveFirst
rst.AddNew
rst!fldDte = (DateAdd("ww", I, StartDte))
rst.Update
rst.MoveNext
Next I
rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing
End Sub

 

[raskew]

Don't understand where you're going with this, but consider:

TblDates with field [MyMonth] (text) populated with 12 records, i.e.,
"01/2003" - "12/2003"

The calculated month, inexplicably, starts the last day of the
previous month (per your example)

QuerySQL looks like this:

SELECT tblDates.myMonth AS myMonth, DateValue([MyMonth])-1 AS Week1, DateAdd("w",[Week1],7) AS Week2, DateAdd("w",[Week1],14) AS Week3, DateAdd("w",[Week1],21) AS Week4, DateAdd("w",[Week1],28) AS Week5
FROM tblDates;

and the output looks like this:

MyMonth Week1 Week2 Week3 Week4 Week5
01/2003 12/31/02 1/7/03 1/14/03 1/21/03 1/28/03
02/2003 1/31/03 2/7/03 2/14/03 2/21/03 2/28/03
03/2003 2/28/03 3/7/03 3/14/03 3/21/03 3/28/03
04/2003 3/31/03 4/7/03 4/14/03 4/21/03 4/28/03
05/2003 4/30/03 5/7/03 5/14/03 5/21/03 5/28/03
06/2003 5/31/03 6/7/03 6/14/03 6/21/03 6/28/03
07/2003 6/30/03 7/7/03 7/14/03 7/21/03 7/28/03
08/2003 7/31/03 8/7/03 8/14/03 8/21/03 8/28/03
09/2003 8/31/03 9/7/03 9/14/03 9/21/03 9/28/03
10/2003 9/30/03 10/7/03 10/14/03 10/21/03 10/28/03
11/2003 10/31/03 11/7/03 11/14/03 11/21/03 11/28/03
12/2003 11/30/03 12/7/03 12/14/03 12/21/03 12/28/03

Would that work?

 

[jesseb]

These could definately work. I just have to modify them a bit. The months don't always start on the the first and some go 4 weeks others go 5 but I have all those numbers in a table. I think I can get it from this. Thanks a lot.

 

[jesseb]

raskew-
Is there anyway to do the fifth week only if the column -NumWeeks- in the table -Operations- is 5, otherwise do 4?

 

[jesseb]

I have done it finally, here is the code in case anyone wants to see how it ended up:

In this case I used 2 tables oracle and output.

Sub FillTbl()

Dim DB As Database
Dim Oracle As Recordset
Dim Output As Recordset
Dim StartDte As Date
Dim i As Integer, Period As Integer
Dim units As Double



Set DB = CurrentDb
Set Oracle = DB.OpenRecordset("OracleOps")
Set Output = DB.OpenRecordset("Oracle_Output")


Oracle.MoveFirst

Do

Period = Oracle!Numweeks


For i = 0 To Period - 1


StartDte = Oracle!Month_Start


Output.AddNew

Output!Weeks = (DateAdd("ww", i, StartDte))
Output!Related_Month = Oracle!Month


Output.Update

Next i

Oracle.MoveNext

Loop While Oracle.EOF = False

Oracle.Close
Output.Close

Set Oracle = Nothing
Set Output = Nothing

DB.Close

Set DB = Nothing

End Sub