Find "Last Friday" function works almost 100%

[Juan Correa]

Hello,
I have these two functions (written with the help of some of you guys here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Douglas J. Steele]

I don't seem to be able to reproduce your error.

If you go to the Immediate Window (Ctrl-G), type

?NextPeriod(#2008-12-26#)

and hit Enter, what value do you get? (I get 30 January, 2009)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
I have these two functions (written with the help of some of you guys
here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with
that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd
field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I
can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Dale Fye]

Juan,

You don't really need to calculate the last friday in the month of the
invoice, you just need to find the last friday in the successive month, so
try:

Public Function NextPeriod(InvDate as Date) as Date

NextPeriod = ThisPeriod(DateAdd("m", 1, InvDate)

End Function


----
HTH
Dale

Hello,
I have these two functions (written with the help of some of you guys here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Juan Correa]

Dale,

I get a compile error with your function.

"Expected list separator or )"

Cheers
Juan

Juan,

You don't really need to calculate the last friday in the month of the
invoice, you just need to find the last friday in the successive month, so
try:

Public Function NextPeriod(InvDate as Date) as Date

NextPeriod = ThisPeriod(DateAdd("m", 1, InvDate)

End Function


----
HTH
Dale

Hello,
I have these two functions (written with the help of some of you guys here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Juan Correa]

Never mind the compile error... It just needed an extra ) at the end.

It does; however, come up with the same error as my original function.

12/28/2008 still converts to 01/09/2009 rather than 01/30/2009


Cheers
Sebastian

Dale,

I get a compile error with your function.

"Expected list separator or )"

Cheers
Juan

Juan,

You don't really need to calculate the last friday in the month of the
invoice, you just need to find the last friday in the successive month, so
try:

Public Function NextPeriod(InvDate as Date) as Date

NextPeriod = ThisPeriod(DateAdd("m", 1, InvDate)

End Function


----
HTH
Dale

Hello,
I have these two functions (written with the help of some of you guys here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Dale Fye]

Juan,

My guess is that the problem is in your ThisPeriod function. It seems
unusually long to simply find the last friday of the month associated with a
date. My function looks like:

Public Function LastFriday(SomeDate As Date) As Date

Dim myDate As Date

myDate = DateSerial(Year(SomeDate), Month(SomeDate) + 1, 0)

While Weekday(myDate, vbSunday) <> 6
myDate = DateAdd("d", -1, myDate)
Wend

LastFriday = myDate

End Function

----
HTH
Dale

Never mind the compile error... It just needed an extra ) at the end.

It does; however, come up with the same error as my original function.

12/28/2008 still converts to 01/09/2009 rather than 01/30/2009


Cheers
Sebastian

Dale,

I get a compile error with your function.

"Expected list separator or )"

Cheers
Juan

Juan,

You don't really need to calculate the last friday in the month of the
invoice, you just need to find the last friday in the successive month, so
try:

Public Function NextPeriod(InvDate as Date) as Date

NextPeriod = ThisPeriod(DateAdd("m", 1, InvDate)

End Function


----
HTH
Dale



:

Hello,
I have these two functions (written with the help of some of you guys here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Juan Correa]

Dale,

I know that the ThisPeriod function could be optimized, just didn't get
around to that yet as I wanted to get the whole thing working properly first.


Douglas...
Testing for 12/26/08 will yield the correct result because 12/26/08 is the
date of the last Friday in December 2008.

If you test for 12/27 through 31/08 you will get the error I mention.

Cheers
Juan

I don't seem to be able to reproduce your error.

If you go to the Immediate Window (Ctrl-G), type

?NextPeriod(#2008-12-26#)

and hit Enter, what value do you get? (I get 30 January, 2009)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
I have these two functions (written with the help of some of you guys
here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with
that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday date.
2. If invoice date is after Last Friday date then use Last Friday date of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd
field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months where
the invoice date is after the Last Friday for that particular month will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I
can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Douglas J. Steele]

All I'm asking you to test is that NextPeriod returns the correct value.

Since it's intended to return the date of the last Friday in the following
month, you should get 30 January, 2009 for any value passed between
#2008-12-01# and #2008-12-31#.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas...
Testing for 12/26/08 will yield the correct result because 12/26/08 is the
date of the last Friday in December 2008.

If you test for 12/27 through 31/08 you will get the error I mention.

Cheers
Juan

I don't seem to be able to reproduce your error.

If you go to the Immediate Window (Ctrl-G), type

?NextPeriod(#2008-12-26#)

and hit Enter, what value do you get? (I get 30 January, 2009)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
I have these two functions (written with the help of some of you guys
here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in
Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a
query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with
that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday
date.
2. If invoice date is after Last Friday date then use Last Friday date
of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last
Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd
field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months
where
the invoice date is after the Last Friday for that particular month
will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I
can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Juan Correa]

Douglas,

I was a bit too quick with my reply.

You are right in the fact that the test produces the correct results.

My issue is a bit different though...

What I am trying to accomplish is the following:

If invoice date is before the date of the last Friday for the given month
then return the date for the last Friday in that month.
If the invoice date is after the date of the last Friday for the given month
then return the date for the Last Friday in the next month.

If I use this expression in a calculated field in a query
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

It produces the correct results except for the dates between 12/27/2008 and
12/31/2008... for those dates it produces a result of 01/09/2009.

I'm not sure why this is happening...

But I think the issue is with the ThisPeriod() function.
I tested in the immediate window and got the following results
?ThisPeriod(#2008-12-26#)
12/26/2008 --> Correct since 12/26/08 is the last Friday of December 08

?ThisPeriod(#2008-12-27#)
12/26/2008 --> Incorrect since 12/27/08 is after the last Friday of December
08, it should have returned 1/30/09

?ThisPeriod(#2008-12-28#)
1/9/2009 --> Incorrect. It figures out that 12/28/08 is after the last
Friday of December, but returns the wrong date.


Cheers
Juan

All I'm asking you to test is that NextPeriod returns the correct value.

Since it's intended to return the date of the last Friday in the following
month, you should get 30 January, 2009 for any value passed between
#2008-12-01# and #2008-12-31#.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas...
Testing for 12/26/08 will yield the correct result because 12/26/08 is the
date of the last Friday in December 2008.

If you test for 12/27 through 31/08 you will get the error I mention.

Cheers
Juan

I don't seem to be able to reproduce your error.

If you go to the Immediate Window (Ctrl-G), type

?NextPeriod(#2008-12-26#)

and hit Enter, what value do you get? (I get 30 January, 2009)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,
I have these two functions (written with the help of some of you guys
here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in
Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a
query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated with
that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday
date.
2. If invoice date is after Last Friday date then use Last Friday date
of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last
Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the calculatd
field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months
where
the invoice date is after the Last Friday for that particular month
will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but I
can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Douglas J. Steele]

I do not see any way that what you could be getting 01/09/2009.

Your calculated field either returns the results of the ThisPeriod function,
or the results of the NextPeriod function, and neither of those two
functions seem capable of returning that particular date.

That's why I'm trying to break the problem down into small pieces: so that
we can determine what's causing the problem.

In order to do that, I'd like you to test what values are being returned by
the two functions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I was a bit too quick with my reply.

You are right in the fact that the test produces the correct results.

My issue is a bit different though...

What I am trying to accomplish is the following:

If invoice date is before the date of the last Friday for the given month
then return the date for the last Friday in that month.
If the invoice date is after the date of the last Friday for the given
month
then return the date for the Last Friday in the next month.

If I use this expression in a calculated field in a query:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

It produces the correct results except for the dates between 12/27/2008
and
12/31/2008... for those dates it produces a result of 01/09/2009.

I'm not sure why this is happening...

But I think the issue is with the ThisPeriod() function.
I tested in the immediate window and got the following results
?ThisPeriod(#2008-12-26#)
12/26/2008 --> Correct since 12/26/08 is the last Friday of December 08

?ThisPeriod(#2008-12-27#)
12/26/2008 --> Incorrect since 12/27/08 is after the last Friday of
December
08, it should have returned 1/30/09

?ThisPeriod(#2008-12-28#)
1/9/2009 --> Incorrect. It figures out that 12/28/08 is after the last
Friday of December, but returns the wrong date.


Cheers
Juan

All I'm asking you to test is that NextPeriod returns the correct value.

Since it's intended to return the date of the last Friday in the
following
month, you should get 30 January, 2009 for any value passed between
#2008-12-01# and #2008-12-31#.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas...
Testing for 12/26/08 will yield the correct result because 12/26/08 is
the
date of the last Friday in December 2008.

If you test for 12/27 through 31/08 you will get the error I mention.

Cheers
Juan

:

I don't seem to be able to reproduce your error.

If you go to the Immediate Window (Ctrl-G), type

?NextPeriod(#2008-12-26#)

and hit Enter, what value do you get? (I get 30 January, 2009)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,
I have these two functions (written with the help of some of you
guys
here)

Public Function ThisPeriod(ByVal dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = Year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in
Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
Year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
ThisPeriod = DateAdd("ww", -1, dtmBaseDate)
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = ThisPeriod(InvDate)
NextPeriod = ThisPeriod(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And using those two functions I have created a calculated field in a
query
that looks like this:

lastFriday:
IIf([tbl_invoices]![invoiceDate]<=ThisPeriod([tbl_invoices]![invoiceDate]),ThisPeriod([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

What that calculated field outputs is the "Last Friday" associated
with
that
invoice date.
Here are the conditions:
1. If invoice date is before Last Friday date then use Last Friday
date.
2. If invoice date is after Last Friday date then use Last Friday
date
of
next month.

The whole thing works except in the following instance:
When the month is December and the invoice date is after the "Last
Friday"
for December.
Example:
December 2008 ---> Last Friday for that period is 12/26/2008
If I have an invoice date 12/27/2008 through 12/31/2008 the
calculatd
field
fails and produces this result: 01/09/2009.
Again this only happens in the month of December. Any other months
where
the invoice date is after the Last Friday for that particular month
will
produce the desired result (The last Friday of the next month).

I'm pretty sure the error comes from the NextPeriod() function, but
I
can't
figure out the correction.

Any help on this would be greatly appreciated.

Cheers
Sebastian

 

[Juan Correa]

Douglas, Dale,

Thank you both very much for your help. It turns out all I needed was two
other sets of eyes telling me that some thing did not make sense so that I
could figure it out.


I got rid of the whole ThisPeriod() function and implemented the
LastFriday() function that Dale suggested


So after the changes I ended up with two functions:

Public Function LastFriday(SomeDate As Date) As Date
Dim myDate As Date
myDate = DateSerial(Year(SomeDate), Month(SomeDate) + 1, 0)
While Weekday(myDate, vbSunday) <> 6
myDate = DateAdd("d", -1, myDate)
Wend
LastFriday = myDate
End Function

Public Function NextPeriod(InvDate As Date) As Date
Dim dtmPeriodEnd As Date
dtmPeriodEnd = LastFriday(InvDate)
NextPeriod = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) + 1, 1))
End Function

And with those two functions I then changed the calculated field in my query
to look like this:
LastFriday:
IIf([tbl_invoices]![invoiceDate]<=LastFriday([tbl_invoices]![invoiceDate]),LastFriday([tbl_invoices]![invoiceDate]),NextPeriod([tbl_invoices]![invoiceDate]))

That yields the expected results for any date. Yay!!!!!

My code is much simpler now, and my query works like a charm...

Thanks again guys... I would have been spinning my wheels four hours if it
weren't for this great community.

Cheers
Juan Correa