Query Mondays (Dates) in last 52 weeks

[Solo7]

My main query has a list of events with the corresponding dates that they occured.
I want to create a second query that shows Monday’s date of the current week and the Monday date of the preceding 51 weeks.

How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.

Then I want to create a third query by combining the first two such that the with the dates and events main query are displayed and grouped by the Monday dates for the year query.

2nd Qry Main Query
Mondays Event # Date
8/6/2007 78932 8/6/2007
8/13/2007
8/20/2007
8/27/2007
9/3/2007
9/10/2007
9/17/2007
9/24/2007
10/1/2007
10/8/2007 80899 10/8/2007
10/15/2007
10/22/2007
10/29/2007 81791 11/1/2007
11/5/2007 81991 11/8/2007
11/12/2007
11/19/2007
11/26/2007
12/3/2007 82697 12/5/2007
12/3/2007 82748 12/6/2007
12/3/2007 82786 12/7/2007
12/10/2007
12/17/2007 83111 12/18/2007
12/24/2007
12/31/2007 83553 1/4/2008
12/31/2007 83561 1/4/2008

 

[ajetrumpet]

How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.

Solo,

A query is for analyzing data that is already in your tables. You will probably have to populate some sort of object with these values, and then get them into a table or query that way.

Maybe populating an array with a loop would do the trick.

 

[DCrake]

Simple Software Solutions

Hi

Here is a function that will give you what you want

Function ListMondays(Fld As Control, id As Variant, _
row As Variant, col As Variant, Code As Variant) _
As Variant
Dim intOffset As Integer
Dim szDate As Date
szDate = DateAdd("m", -12, Now())

Select Case Code
Case acLBInitialize ' Initialize.
ListMondays = True
Case acLBOpen ' Open.
ListMondays = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListMondays = 9
Case acLBGetColumnCount ' Get columns.
ListMondays = 1
Case acLBGetColumnWidth ' Get column width.
ListMondays = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intOffset = Abs((9 - Weekday(szDate)) Mod 7)
ListMondays = Format(szDate + _
intOffset + 7 * row, "dd/mm/yyyy")
End Select
End Function


Place this function in a module then go to the row source type of your combo box and enter ListMondays (Don't use '=').

CodeMaster:http://www.icraftlimited.co.uk

 

[Solo7]

Thanks for the feedback. I'm still working on it.

 

[Solo7]

Although I appreciate your help what you have suggested is a bit beyond me. Any chance we could do this with say a table that has a default of Today() and use the Query push the 52 dates through?

 

[Mile-O]

Although I appreciate your help what you have suggested is a bit beyond me. Any chance we could do this with say a table that has a default of Today() and use the Query push the 52 dates through?

Solo7, do you have the 52 dates in a table already? If not, you can't do this with a query. They can only work with data that is present in your tables.

 

[ajetrumpet]

Although I appreciate your help what you have suggested is a bit beyond me. Any chance we could do this with say a table that has a default of Today() and use the Query push the 52 dates through?

You might want to be a bit more specific here Solo. I don't think anyone quite understands what you are talking about...

 

[Solo7]

Solo7, do you have the 52 dates in a table already? If not, you can't do this with a query. They can only work with data that is present in your tables.

I understand. I was hoping that someone had a solution like a table that calculated 52 mondays from this monday back and that I could use that to add to my current query so that the existing event dates would be grouped in the combined query by Date>last monday and Date<next monday.

 

[Solo7]

For example, if we make a table with one field which has a default value of NOW(), a second field with a default value of Now()-7, a third with Now()-14... we end up with a 52 field record with today's date in the first field and consecutive fields 7 days earlier than the last, through 52 weeks. That way it is always current to the day without manual update.

If it were possible then to convert the single record into 52 records with one date in each record and bring it into a query with the main dataset so that they are grouped by the Now() dates.

The best explaination I can give is that I want to build a chart from the data showing 52 periods, even though I may have actual data from every other week.

 

[raskew]

Solo / Adam

The following sub, copied/pasted to a standard module and called as shown, will create/recreate tblMondays and populate it with date of current Monday and preceding 51 Mondays.

You don't want 52 fields -- that's a spreadsheet and defies most rules of database normalization -- rather 52 records.

Unless time plays into your usage, use Date() rather than Now(). See this MSKB article as to how dates/times are stored in Access:
http://support.microsoft.com/kb/q130514/

Today() is not an Access function.

Adam -

If you attempt to create tabledef("datetable") and it already exists, your code will generate an error.

Function Test() should be a sub, since it doesn't produce 'output'.

Solo7 is looking for the preceding Mondays, your code returns Mondays in the future.

olddate = Date() -- What does that do?

Please don't send me anymore private messages. I'll publish my results (re business/holiday dates) when they're ready to go.

Bob

Public Sub Mondays()
'**************************************************
'Purpose:   Create/recreate tblMondays and populate
'           with current Monday and preceding 51 Mondays
'Coded by:  raskew
'Input:     from debug window: Call Mondays
'Output:    tblMondays populated as described above
'**************************************************

Dim db      As Database
Dim rs      As Recordset
Dim dteDate As Date
Dim strSQL  As String
Dim tName   As String
Dim n       As Integer

    Set db = CurrentDb
    'create/recreate table
    On Error Resume Next
    tName = "tblMondays"
    'next line will delete tblMondays - modify if necessary
    db.Execute "DROP TABLE " & tName & ";"
    strSQL = "CREATE TABLE " & tName & " (DateID AUTOINCREMENT, MyDate DATETIME);"
    db.Execute strSQL
    Set rs = db.OpenRecordset(tName)
    dteDate = Date - WeekDay(Date) + 2
    For n = 1 To 52
       With rs
          .AddNew
          !MyDate = dteDate
          .Update
          dteDate = dteDate - 7
       End With
   Next n
   rs.Close
   db.Close
   Set db = Nothing
End Sub