Discussion Locked

Access 2007 - Function to return date of Friday for dates in same week

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

What I'm trying to is a little difficult to word, so bear with me please!

I'm adding a chart to my Access report to show how our production values have changed from week to week. My boss hates that the axis of the chart shows the weeks in their numbered form, like 51, 52, 1, etc. We define our weeks by Friday's date and I'd like to see those dates on that axis. I couldn't see how to do it just with the chart, so I figure it would be best if I can write an expression in the query itself to give me that Friday's date, then I can use that column for the axis on the chart.

Does anyone have any idea how I could write an expression like that? Or how I could set the date differently on the chart?

6 replies

by Former member

This is a copy from another forum:
Hope all works out.

There have been a couple of examples in this thread that has provided a return of a date from an occurrence of a particular day of the week, such as "3rd Wednesday in Month" function earlier. The idea was to simplify the function to a tight algorithm so it could be used for any Day of Week, as well as the occurrence of that particular Day of Week within the month (i.e. "First Tuesday of month", "Third Sunday of month", etc.). In addition, the function can also return the LAST particular occurrence of a Day of Week within the month (i.e. "Last Friday of month").

The function is called "fncReturnDate." It takes three required arguments:

lngWeekday--The Day of Week needed for the return, in number format (i.e. Sun=1, Mon=2, ...Sat=7) You can use VB Constants to clarify code. For instance, vbSunday=1, vbMonday=2,...vbSaturday=7.

lngOccurrence--There are actually 2 different scenarios for this input, both in number format.

1. If you are looking for a particular occurrence of the Day of Week in the month, then enter a number of the particular occurrence of the Day of Week you are looking for (i.e. 1="First (Day of Week) in month, 2="2nd (Day of Week) in month", etc.)
2. If you are looking for the LAST occurrence of the Day of Week in the month, then set lngOccurrence to 0

datMonth--Date containing the Month and Year to find the dates required. Note that although a day is required (as part of the date passed), it is not used within the function itself

Finally, there is an optional argument: blnIsInMonth. This is an internal argument return that indicates whether the date returned is the same as the date originally passed. If months are the same, then the return value for blnIsInMonth is True, otherwise, the answer is False.

The reason for this boolean argument is that the function will always return a date. If the desired result is to find, say, the 5th Monday in January 2008, the return is actually February 4 2008. Since the months do not match, however, blnIsInMonth would return false. This will allow you to write code from the calling function to warn the user, i.e. "There are not 5 occurences of Monday in January 2008".

Here is the function:

Code:

Public Function fncReturnDate( _
ByVal lngWeekday As Long, _
ByVal lngOccurrence As Long, _
ByVal datMonth As Date, _
Optional ByRef blnIsInMonth As Boolean) As Date
'-----------------------------------------------------------------------------------------
' Procedure : fncReturnDate
' Created : 1/10/2008 17:34
' Reference : fncReturnDate*
' Author : Michael Reese
' Input(s) : lngWeekday--The Day of Week needed for the return, in number format.
' : (i.e. Sun=1, Mon=2, ...Sat=7) (HINT: Use VB Constants to clarify code)
' : (i.e. vbSunday=1, vbMonday=2,...vbSaturday=7)
' : lngOccurrence--What occurence of the Day of Week in the month to use
' : (i.e. 1="First (Day of Week) in month, 2="2nd (Day of Week) in month", etc.
' : ****** OR ******
' : If lngOccurrence = 0, then the function will return the date of LAST
' : particular Day of week in the month
' : datMonth--Date containing the Month and Year to find the dates required
' : Note: Although a day is required, it is not used in the calculation
' : blnIsInMonth (optional)--Returns true if month of datMonth is the same as the return
' Output(s) : Function returns the date of the particular day
' : blnIsInMonth--True if month of date returned is same as month of datMonth
' Purpose : Returns the date of a particular day in a given month
' : i.e. Second Wednesday in Dec 2007 returns "December 12 2007" (in date format)
'-----------------------------------------------------------------------------------------

'|<------ 90-character width -------------------------------- 90-character width ------->|

PROC_DECLARATIONS:
Dim datReturnDate As Date
Dim datFirstDay As Date
Dim datLastDay As Date
Dim lngFirstWeekday As Long
Dim lngReturnDay As Long

PROC_START:
On Error GoTo PROC_ERROR

PROC_MAIN:
If lngOccurrence > 0 Then
'find the Nth (Day of Week) of the Month
datFirstDay = DateSerial(Year(datMonth), Month(datMonth), 1)
lngReturnDay = ((7 * ((lngOccurrence - 1) + Abs(lngWeekday < Weekday(datFirstDay)))) + _
(lngWeekday - Weekday(datFirstDay)) + 1)
Else
'find the last (Day of Week) of the Month
datLastDay = DateSerial(Year(datMonth), Month(datMonth) + 1, 0)
lngReturnDay = (Day(datLastDay) - (Weekday(datLastDay) - lngWeekday)) + _
(7 * (lngWeekday > Weekday(datLastDay)))
End If

datReturnDate = DateSerial(Year(datMonth), Month(datMonth), lngReturnDay)

'internal return argument blnIsInMonth
If Month(datReturnDate) = Month(datMonth) Then
blnIsInMonth = True
End If

PROC_EXIT:
fncReturnDate = datReturnDate
Exit Function

PROC_ERROR:
MsgBox "Error " & Err.Number & " (" & _
Err.Description & ")" & vbCrLf & vbCrLf & _
"Procedure: fncReturnDate" & vbCrLf & _
"Module: basUtilities"
GoTo PROC_EXIT

Please post back if you have anymore problems or questions.

by jsuess

Thanks so much. But, I'm still fairly new to Access, and I've never written a bit of VBA code in my life. Is there a variation that I could use in the expression builder?

Knowing me, I'll mess up all that code in some catastrophic way. Thanks again.

by dryflies

Yes there are functions in the expression builder.
A good idea would be for you to create a "test" database and just play with it to learn what you can do with access. I ended up creating a players, coaches and volunteers database for our local little league. It makes life so much easier for them and I got to learn things about access. there are tutorials and all kinds of online lessons in access. Oregon state university has an online class for M$ access.

by dryflies

you should indicate so. I like to help people, but getting our "thumbs up" numbers up is a motivation.

by dryflies

unless you want to use VBA, you will have to use the expression builder or create your own math formula.
in the field at which you want the date inserted, right click and select build. go down the list of insertable items to built in functions, select date and time functions and piece together the combination that will provide you with the correct date. I would get todays serial date and numerical day of week. then use those to adjust the serial date to the one for friday. finally, convert and format to fit your boss' desire and you're done.

If you wish to do the raw calculations you will have to consider leap year in your mathematics and I am not sure how you can do that without VBA. However the first method above does not IMO produce a VBA module if you just nest functions and math in a formula which could be a field of a query or almost any object in the DB app.

This discussion is closed to new replies.