Select the first and last day of week

[sal21]

In a table of Access have a filed DATA.
I can have duplicate date:

similar:
01/01/2013
01/01/2013
02/02/2012...

ecc...

i need to extract from the filed in table only the perfect block of date of week from moonday to fryday

example:
01/05/2013
01/05/2013
01/05/2013
02/05/2013
03/05/2013

not is complete! not extract

but

20/05/2013
20/05/2013
20/05/2013
21/05/2013
21/05/2013
22/05/2013
23/05/2013
24/05/2013

is a complete set of week dayS from monnday to friday....!

Fill the var initweek="20/05/2013" and the var endweek="24/05/2013"

Naturally i can have a lot of dates in field

store in array the complete set of week... for subseguent loop to fill a combobox, for example:

01/04/2013 - 05/04/2013
20/05/2013 - 24/05/2013
27/05/2013 - 31/05/2013

ecc...

I hope understand me.

[HansV]

I'll try to write code for it, but it might take some time.

[sal21]

I'll try to write code for it, but it might take some time.

abbandoned the first way

now:

Code: Select all

    SQL = "SELECT MONTH([DATA_REALE]), YEAR([DATA_REALE]) FROM DATE_TAB GROUP BY MONTH([DATA_REALE]), YEAR([DATA_REALE]) ORDER BY MONTH([DATA_REALE]), YEAR([DATA_REALE])"
    DoEvents
    Set RS1 = DB.OpenRecordset(SQL, dbOpenSnapshot)
    
    With Me.SETTIMANA
        .Clear
        Do While Not RS1.EOF
        
    MM = Format(RS1.Fields(0).Value, "#00")
    YYYY = RS1.Fields(1).Value
        
        For dtmDate = DateSerial(YYYY, MM, 1) To DateSerial(YYYY, MM + 1, 0)
            If Weekday(dtmDate) = vbMonday Then
                Debug.Print dtmDate & " - " & DateAdd("D", 5, dtmDate)
                '.AddItem dtmDate & " - " & DateAdd("D", 5, dtmDate)
                Z = Z + 1
            End If
        Next
            
            RS1.MoveNext
        Loop
    End With

in effetct i need to loop all days based year and month and loop the relatred date but not sure tath work...

[HansV]

What are you trying to do now? Are you trying to generate a list of weeks regardless of whether they occur in the table or not?

[sal21]

What are you trying to do now? Are you trying to generate a list of weeks regardless of whether they occur in the table or not?

yes!

[HansV]

Why not use code like this?

Code: Select all

    Dim y As Long
    Dim d As Date
    Me.Settimana.Clear
    ' Current year
    y = Year(Date)
    ' First day of the year
    d = DateSerial(y, 1, 1)
    ' First Monday
    d = d + 7 - Weekday(d, vbTuesday)
    Do
        ' Add item
        Me.Settimana.AddItem d & " - " & (d + 4)
        ' Move one week
        d = d + 7
    Loop Until Year(d) > y