MS Access Need help with date expression for a report

I have a table with every day dates:
6-13-13
6-14-13
6-15-13
Etc.

How can I produce a report to display weekly's from the table above? I.E.
Week of June 10
Week of June 17
Week of June 24
Etc.
DJPr0Asked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

a simplfied version of my code
MondayDate: "Week of " & Format([Field1]-(([Field1]-2) Mod 7),"mmm dd")

Open in new window

Regards
0
 
Rey Obrero (Capricorn1)Commented:
you will need a function to do that, place this codes in a regular module


Function MondayDate(CurrentDate) As String

   If VarType(CurrentDate) <> 7 Then
      MondayDate = Null
   Else
      Select Case Weekday(CurrentDate)
         Case 1       ' Sunday
            MondayDate = "Week Of " & Format(CurrentDate - 6, "mmm dd")
         Case 2       ' Monday
            MondayDate = "Week Of " & Format(CurrentDate, "mmm dd")
         Case 3 To 7  ' Tuesday..Saturday
            MondayDate = "Week Of " & Format(CurrentDate - Weekday(CurrentDate) + 2, "mmm dd")
       End Select
   End If
End Function



create a query like this

select [dateField], MondayDate([datefield])
from tableName




.
0
 
Rgonzo1971Commented:
Hi,

As Field in a query Field as your Date

MondayDate: "Week of " & Format([Field1]-([Field1] Mod 7)+2,"mmm") & " " & Day([Field1]-([Field1] Mod 7)+2)

Open in new window

like
SELECT Table1.Date, "Week of " & Format([Date]-([Date] Mod 7)+2,"mmm") & " " & Day([Date]-([Date] Mod 7)+2) AS MondayDate
FROM Table1;

Open in new window

Regards
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DJPr0Author Commented:
Rgonzo1971  One little problem.

Saturdays date will show in the following week: (Mon - Friday dates work fine)

Week of: Jun 17
                                 Dave              Replace Window          6/15/2013

                         

 Week of: Jun 24


Is it possible to display:
Week of: June 2 - June 8
Week of: June 9 - June 15
Etc.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
did you try the function i posted?

here is the revised function to display  

Week Of: Jun 02 - Jun 08


Function WeekDates(CurrentDate) As String ' Sunday to Saturday range

   If VarType(CurrentDate) <> 7 Then
      WeekDates = Null
   Else
      Select Case Weekday(CurrentDate)
         Case 1      ' Sunday or Saturday
            WeekDates = "Week Of: " & Format(CurrentDate, "mmm dd") & " - " & Format(CurrentDate + 6, "mmm dd")
         Case 2 To 6       ' Monday.. Friday
            WeekDates = "Week Of: " & Format(CurrentDate - Weekday(CurrentDate) + 1, "mmm dd") & " - " & Format(CurrentDate - Weekday(CurrentDate) + 7, "mmm dd")
         Case 7  ' Saturday
            WeekDates = "Week Of: " & Format(CurrentDate - Weekday(CurrentDate) + 1, "mmm dd") & " - " & Format(CurrentDate, "mmm dd")
       End Select
   End If
End Function
0
 
DJPr0Author Commented:
capricorn1 - I created the module.

Do I need to create a separate query to supply the report?

Not sure where I place the query below.


select [dateField], WeeklyDate([datefield])
from tableName
0
 
Rey Obrero (Capricorn1)Commented:
<Do I need to create a separate query to supply the report? >

what is the record source of your report ?

if it is a query,  post the SQL statement of the query.

if it is a table, you need to create a query and change the Record Source of the report.

after you created the query, post the SQL statement of the query.

so we can correct it if needed.



.
0
 
DJPr0Author Commented:
Here is my select statement for my new query:

SELECT tblDates.RefDate, WeekDates([datefield])
FROM tblDates;


I receive an error: Undefined function "Weekdates" in expression.

The Weekdates module is in my list of modules.
0
 
Rey Obrero (Capricorn1)Commented:
what name did you give to your module?


use this query


SELECT tblDates.RefDate, WeekDates([RefDate])
FROM tblDates;
0
 
Rgonzo1971Commented:
Hi,

Sorry, I forgot the weekend

MondayDate: "Week of " & Format([Field1]-(([Field1]-2) Mod 7),"mmm") & " " & Day([Field1]-(([Field1]-2) Mod 7))

Open in new window

Regards
0
 
Helen FeddemaCommented:
This would be ideal for a PivotTable, grouped by week.  Unless you are using Access 2013, where PivotTables have been dropped.
0
 
DJPr0Author Commented:
Thanks experts, both solutions worked!
0
All Courses

From novice to tech pro — start learning today.