Figure out the first week of the month 2

[Mack2]

I need to calculate data for each week of the month. So if the first week of the month only has a thursday and friday, then it should total just those two day. Then for week two, it would total Monday through Friday.
PLEASE HELP! THANKS in advance

 

[CautionMP]

Mack2,
I have no idea where you want to calculate this but you might look at the [tt]DatePart()[/tt] function. If you use the "[tt]ww[/tt]" interval it will return what week a specific date is in.

If used in a query you could then group on the week number to total your records.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

There may be a few ideas in this:

Dim CheckDate, FirstDayOfMonth, FirstDayOfWeek, LastDayOfWeek, NextWeek
CheckDate = Date 'Pick a date
FirstDayOfMonth = DateSerial(Year(CheckDate), Month(CheckDate), 1)
LastDayOfWeek = ([FirstDayOfMonth] - Weekday([FirstDayOfMonth], vbSunday)) + 7
Debug.Print "Week 1: " & FirstDayOfMonth & " - " & LastDayOfWeek
Count = 2
Do While True
    FirstDayOfWeek = LastDayOfWeek + 1
    LastDayOfWeek = FirstDayOfWeek + 6
    If Month(LastDayOfWeek) = Month(CheckDate) Then
        Debug.Print "Week " & Count & ": " & FirstDayOfWeek & " - " & LastDayOfWeek
        Count = Count + 1
    Else
        If Month(FirstDayOfWeek) = Month(CheckDate) Then
            Debug.Print "Week " & Count & ": " & FirstDayOfWeek & " - " _
            & DateSerial(Year(CheckDate), Month(CheckDate) + 1, 0)
        End If
        Exit Sub
    End If
Loop

 

[Mack2]

Thanks to both of you. I will try both of them. The calculation will be in a query to answer your question cautionmp
THANKS AGAIN...I will let you know the results

 

[Mack2]

Remou ..... I need to calculate totals for the next 3 months broken down in weeks. Your code looks like it will work, but where would I put it? Or How would I use it?
THANKS!

CautionMP...very cool function, if it would accept a date like 7/1/06 as the last argument, then it would work. But it does not, it will only take 4 available arguments. Unless I am missing something
Thanks

 

[Remou]

It was intended as a few ideas, but you could add a line to output totals for example, instead of:
[tt]Debug.Print "Week " & Count & ": " & FirstDayOfWeek & " - " & LastDayOfWeek[/tt]

You could say:
[tt]Debug.Print "Week " & Count & ": " & DSum("TotalField","tblTable","DateField Between #" & FirstDayOfWeek & "# And #" & LastDayOfWeek & "#")[/tt]

 

[Mack2]

Ranou...your code worked great! I used the first few lines of code to figure out the first week, and ran from there. THANK YOU VERY MUCH!