Help with calculating the last friday of the month in a query

[Astron2012]

Hi,

I want to create a query that will bring up data from a table automatically for what we call the working month. This is from the last friday of the previous month + 1 to the last friday of the current month.

Does anyone know how I can write this calculation in the query design mode under the 'MailingDate' field? I would like to use something like the Now() function so the user won't need to enter any dates.

Any help with this would be much appreciated.



Cheers

[orange]

Here is a subroutine showing what is involved.
I have set it to work from Today's Date and calculate the Last Friday this month and Last Friday Last Month

'---------------------------------------------------------------------------------------
' Procedure : j20120611
' Author    : Jack
' Date      : 11/06/2012
' Purpose   : To provide the Last Friday in the month associated with a supplied Date, and
'             the Last Friday in the previous month
'---------------------------------------------------------------------------------------
'
Sub j20120611()
      Dim mydate As Date
      Dim LastFridayThisMonth As Date
      Dim LastFridayLastMonth As Date

10       On Error GoTo j20120611_Error

20    mydate = Date 'set up for specific request - based on today's date

'This will work with a supplied Date as MyDate
'   "23/21/4/3" '#1/45/2002# 'for testing  #6/11/2012#  #12/24/2012#

30    If IsDate(mydate) Then
40     LastFridayThisMonth = DateAdd("d", 1 - DatePart("w", DateSerial(Year(mydate), Month(mydate) + 1, 0), 6), DateSerial(Year(mydate), Month(mydate) + 1, 0))
50     LastFridayLastMonth = DateAdd("d", 1 - DatePart("w", DateSerial(Year(mydate), Month(mydate), 0), 6), DateSerial(Year(mydate), Month(mydate), 0))
60    Else
70      MsgBox "No valid Date supplied...Error....Try again", vbOKOnly
80    End If

90    Debug.Print "This month  " & LastFridayThisMonth & vbCrLf & "Last month  " & LastFridayLastMonth

100      On Error GoTo 0
110      Exit Sub

j20120611_Error:
120    If Err.number = 13 Then
130       MsgBox " You did not enter a Date datatype ....type mismatch"
140    Else
150       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure j20120611 of Module AWF_Related"
160    End If

End Sub

You could make this into a Function, and pass a parameter such as WhichMonth -- This or Last -- and retrieve the appropriate Date.
if you need more info just post.

[Astron2012]

Thanks, I have created a public sub with that. Do you know what I need to do to get it to run when I run the query, or how I add it to the parameters?

Many Thanks

[orange]

I don't understand your question as stated. If you want to use something in a query, I think you need to create a function.
Here is the procedure set up as a function. You have to pass a parameter eith "Last" or "Current" to get the respective Date.

There is a test routine at the end of the function.

'---------------------------------------------------------------------------------------
' Procedure : j20120611
' Author    : Jack
' Date      : 11/06/2012
' Purpose   : To provide the Last Friday in the month associated with a supplied Date, and
'             the Last Friday in the previous month
'
'Parameter   "Last"       to get last month's last Friday
'                 "Current"  to get this month's last Friday
'---------------------------------------------------------------------------------------
'
Function j20120611(WhichMonth As String) As Date
      Dim mydate As Date
      Dim LastFridayThisMonth As Date
      Dim LastFridayLastMonth As Date

10       On Error GoTo j20120611_Error

20    mydate = Date 'set up for specific request - based on today's date

'This will work with a supplied Date as MyDate
'   "23/21/4/3" '#1/45/2002# 'for testing  #6/11/2012#  #12/24/2012#

30    If IsDate(mydate) Then
40     LastFridayThisMonth = DateAdd("d", 1 - DatePart("w", DateSerial(Year(mydate), Month(mydate) + 1, 0), 6), DateSerial(Year(mydate), Month(mydate) + 1, 0))
50     LastFridayLastMonth = DateAdd("d", 1 - DatePart("w", DateSerial(Year(mydate), Month(mydate), 0), 6), DateSerial(Year(mydate), Month(mydate), 0))
60       If WhichMonth = "Current" Then j20120611 = LastFridayThisMonth
70       If WhichMonth = "Last" Then j20120611 = LastFridayLastMonth

80    Else
90      MsgBox "No valid Date supplied...Error....Try again", vbOKOnly
100   End If

110   Debug.Print "This month  " & LastFridayThisMonth & vbCrLf & "Last month  " & LastFridayLastMonth

120      On Error GoTo 0
130      Exit Function

j20120611_Error:
140    If Err.number = 13 Then
150       MsgBox " You did not enter a Date datatype ....type mismatch"
160    Else
170       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure j20120611 of Module AWF_Related"
180    End If

End Function

Sub testj20120611()
Debug.Print "LastMonth Last Friday was   " & j20120611("Last")
Debug.Print "ThisMonth Last Friday will be  " & j20120611("Current")

End Sub

[Astron2012]

Hi, Thanks, sorry.... I'm not very good with functions and queries. What I mean is how do I associate the function with the query so that when I run the query the function will run too?
Do I have to add some code in the query's SQL view?

Many Thanks for your help

[orange]

Show us your query attempts. Did you try to place the code I posted into a module? Did you try to run the

Sub testj20120611() routine?
I'll try to create a scenario showing how this function would work with "my view of your situation".
Suppose you have a table "MyTable" and suppose that table contains fields - EmployeeName, MailingDate, ItemsSent, and other fields.
And suppose you had data for Bob, Bill, Jim, John and Mary for each month so far this year.

To find the information for employees for Last Month ( we are now in June) you could write

Select * from MyTable where MailingDate = j20120611("Last")


This would select all info in your table where the MialDate was the LastFriday Last Month.

j20120611("Last") will return the Date of the LastFriday Last month.

[Astron2012]

OK I understand now.
I put the =j20120611("Last") under the criteria of the DateMailed but unfortunately it doesn't bring up any of the data when I run the query.
I'm not sure how to perform the test, I pasted it under the function and then clicked run but it didn't seem to do anything.

Sorry to be a pain

[orange]

You must put the code for function

j20120611 into a module.

Please post the SQL for your query. Select SQL view from the query window, copy the code and paste it here.

[Astron2012]

Hi,

Yes I pasted the function into a module first. Here is the code for the query....

SELECT tblJobDetails.JobNo, tblJobDetails.[Date Mailed], tblJobDetails.[S,C or F], tblJobDetails.[Final File/Pub Received], tblJobDetails.[Processing Days], tblJobDetails.[DM Member], tblJobDetails.JobTitle, tblJobDetails.[Sponsor Name], tblJobDetails.[Sponsor Staff Number], tblJobDetails.[Sponsor Email], tblJobDetails.[Cost Centre], tblJobDetails.[Engagement Code], tblJobDetails.[Lob/Function], tblJobDetails.[CRM List], tblJobDetails.[Multi Sigs], tblJobDetails.Personalised, tblJobDetails.[Recipients UK], tblJobDetails.[Recipients Overseas], tblJobDetails.[Recipients Internal], tblJobDetails.[Total Recipients], tblJobDetails.[Publications + External Printing], tblJobDetails.[B/W Copies printed by DM], tblJobDetails.[B/W Copies printed by DPC], tblJobDetails.[Colour Copies printed by DM], tblJobDetails.[Colour Copies printed by DPC], tblJobDetails.[Total Letters], tblJobDetails.[Total Copies], tblJobDetails.Letters, tblJobDetails.Reports, tblJobDetails.[Statement/Announcement/Form], tblJobDetails.Remittances, tblJobDetails.Other, tblJobDetails.Labels, tblJobDetails.[Letter/Label/List Prep (mins)], tblJobDetails.[Direct Mail Printing (mins)], tblJobDetails.[DPC Printing (mins)], tblJobDetails.[Publication Order Number], tblJobDetails.[Print Order Number], tblJobDetails.[Postage 1st], tblJobDetails.[Postage 2nd], tblJobDetails.[City Link 2nd], tblJobDetails.Europe, tblJobDetails.[Postage Total], tblJobDetails.[Warehouse Staff (mins)], tblJobDetails.[Additional Temps (mins)], tblJobDetails.[Total Warehouse (mins)], tblJobDetails.[First/Second Class/Internal], tblJobDetails.[Returned Mail], tblJobDetails.[Return Special Instructions]
FROM tblJobDetails
WHERE (((tblJobDetails.[Date Mailed])=j20120611("Last")));

Does it matter what I name the module?

Thanks

[orange]

The name of the module doesn't matter.

Did you try to run the test routine I gave in the post #6? Using something like Debug.Print, as in the test routine, is a common debugging technique to make sure things actually work.

You could create a test query of your own, before running the function with your "production" query, just to ensure it works.

You could have a query like

SELECT * from tblJobDetails where [Date Mailed] = j20120611("Last")

Can you post a few of the values in [Date Mailed] field?

[Astron2012]

Here is a sample. I have dates from last month and this month. I'm not sure how to run test routines, I've never done that before. I did paste the test routine under the the function but i'm guessing that's wrong.

Date Mailed
18/06/2012
08/06/2012
10/06/2012
09/05/2012
04/05/2012

[Astron2012]

Hi, I have added more data to my table and I can see where the function is going wrong now, it only brings up the data from the last friday of the month. Maybe we could modify this to show all records for the last "working month" (for e.g Saturday 28th April to Friday 25th May)? Which i'm guessing would be something like Friday 27th April +1 to Friday 25th May for example. I would need to have the plus one for the first date as our working month begins after the last working friday of the last month. Sorry to sound so confusing.

Thanks so much for your help

[orange]

I created a sample table and some of your dates. To get all records for the period between
the day after the end of Last month and the end of the current month

SELECT *
FROM tblJobDetails
WHERE (((tblJobDetails.DateMailed) Between j20120611("Last")+1 And j20120611("Current")));

Note:
j20120611("Last") returns the Last Friday last month. So if you need 1 day later, just add 1.

Between is an SQL construct that is often used with dates to set inclusive limits on a date range. That is all dates from j20120611("Last")+1 and ending on j20120611("Current") including these end points.

j20120611(parameter ) returns a date (LastFriday in Month).
The Last parameter returns the LastFriday Last Month
The Current parameter returns the LastFriday This Month


To identify all of the Dates in the current month -- from End of last Month +1 to the end of the current month use

Between j20120611("Last")+1 And j20120611("Current")

[Astron2012]

Oh I get it now, it works!!!
Thank you so much for all your help and patience
Fantastic!!!!!

[orange]

You are welcome. Good luck with your project.
A good reference site for Access issues and solutions is
http://www.accessmvp.com/strive4peace/