LastFriday function, exceptions, & year end

[Guest]

You are making more complex than it needs to be. If your date is correctly
formatted in your text box, you don't need the #, and you don't need all the
other stuff you are doing. These two lines will return your close and start
dates, but do not address the exceptions:

CloseDate = LastFriday(Forms!frm_Journal_ID!txtInvDate)
StartDate = dateadd("d",1,lastfriday(dateadd("m",-1, _
lastfriday(Forms!frm_Journal_ID!txtInvDate))))

Now, for exceptions. I do these afterwards, because it is only necessary to
back up the closing dates for March and September. This also means we will
have to adjust the start dates for April and October, right? So all that is
necessary is to subtract one week for the exception periods.

lngException = Month(CloseDate)
If lngException = 3 or lngException = 9 Then
CloseDate = DateAdd("ww", -1, CloseDate)
End If
If lngException = 4 or lngException = 10
StartDate = DateAdd("ww", -1, StartDate)
End If

This all looks very elegant and seems to handle what I want to do, but I am
still lost as to how to make practical use of it. I am pretty new at this. I
am not sure how to use this to filter my database. I can write the start date
and close date expressions in a query, but I don't know how to write the
exception handler.

 

[Guest]

Seems like I recall sending you a post that had two functions that included
the exceptions for start and end dates that included the execptions.

I believe it was something like:

GetStartDate(varTheDate As Variant) as Date
Dim dtmStart As Date

dtmStart = dateadd("d",1,lastfriday(dateadd("m",-1, _
lastfriday(varTheDate))))
lngException = Month(dtmStart)
If lngException = 4 or lngException = 10
dtmStart = DateAdd("ww", -1, dtmStart)
End If
GetStartDate = dtmStart
End Function

The Close Date function would be similar except you would be looking for 3
and 9 for exceptions.

I hope I remembered this correctly.

 

[Guest]

I don't know what happened to it, but I did not get your last post (can't
find it here either). Anyway, I followed your instructions and wrote the
following two functions: Since both use the variable varTheDate and the
lngException, I declared them both in the General Declarations area at the
top.


Function GetStartDate(varTheDate As Date) As Date
Dim dtmStart As Date

varTheDate = Forms!frm_JounalID!txtInvDate
dtmStart = DateAdd("d", 1, LastFriday(DateAdd("m", -1,
LastFriday(varTheDate))))

lngException = Month(dtmStart)
If lngException = 4 Or lngException = 10 Then
dtmStart = DateAdd("ww", -1, dtmStart)
End If

GetStartDate = dtmStart

End Function

Function GetCloseDate(varTheDate As Date) As Date
Dim dtmClose As Date

varTheDate = Forms!frm_JournalID!txtInvDate
dtmClose = LastFriday(varTheDate)

lngException = Month(dtmClose)
If lngException = 3 Or lngException = 9 Then
dtmClose = DateAdd("ww", -1, dtmClose)
End If

GetCloseDate = dtmClose

End Function

But, when I try to check them out in the immediate window, they don't return
anything, not even an error. If I type ?GetStartDate (with the form open and
a date selected into the appropriate textbox) I get nothing. If I type
?varTheDate I also get nothing. Even when I type
?lastfriday(Forms!frm_JournalID.txtInvDate) - the form and box location - I
get an error that Access cannot find the form??? If I type a specific date,
the lastfriday function works, though. Any ideas?

 

[Guest]

OK, I figured out the problem with locating the form, I had misspelled the
form name, but I'm still working on all the other problems.

 

[Guest]

varTheDate does not need to be declared. It is a function argument and is
declared in the definition of the function. It is possible declaring it at a
module level could cause some some confusion.
lngException I forgot to declare in my examples. Although it would be okay
to declare it at the module level, I, personally, would not. I use module
level variables only when procedures need to share the same data. This is
not the case for these two functions. Each is dealing with its own data.

The problem you are having is that you are referencing the form in the
function to assign a value to varTheDate. That is not necessary. It gets
its value from what is passed from the calling routine.

First, here are the functions corrected and working. I tested them in my
immediate window.

Function GetStartDate(varTheDate As Date) As Date
Dim dtmStart As Date
Dim lngException As Long

dtmStart = DateAdd("d", 1, LastFriday(DateAdd("m", -1,
LastFriday(varTheDate))))
lngException = Month(dtmStart)
If lngException = 4 Or lngException = 10 Then
dtmStart = DateAdd("ww", -1, dtmStart)
End If
GetStartDate = dtmStart
End Function

Function GetCloseDate(varTheDate As Date) As Date
Dim dtmClose As Date
Dim lngException As Long

dtmClose = LastFriday(varTheDate)
lngException = Month(dtmClose)
If lngException = 3 Or lngException = 9 Then
dtmClose = DateAdd("ww", -1, dtmClose)
End If
GetCloseDate = dtmClose
End Function

Now here is how you call either of them:

dtmEndDate = GetClosingDate(Date)

If you use Date, it will return the Closing date for the current month. Use
whatever date you want. The only thing we have not addressed it how to
handle dates that are in the current month, but not in the current period.
For example, if you enter today's date (3/14/2006) you will get 3/24/2006,
which would be correct; however if you call using 3/25/2006, you still get
3/24/2006. I don't know if this is correct or not. Let me know if this is
an issue so we can get that working properly.

So, remove the declarations for varTheDate and lngException, paste these two
into a standard module so in case you want to use them in another form or in
a report, they will be visible.

 

[Guest]

Klatuu, I got the whole thing to work. I had written the code as part of the
form object. When it occurred to me to write it to a module it actually
worked. I'm not sure I understand why, but it worked. I do have one question
which may clear up some of the code for me. When I call the function it tells
me the argument is not optional, so I must include in it the form textbox
location, e.g., GetCloseDate(Forms!frm_Journal_ID.txtInvDate); but then,
what's the purpose of setting the variable varTheDate equal to
Forms!frm_Journal_ID.txtInvDate? Or did I screw something up?

 

[Guest]

I posted a couple of times before I got your answer. One was that I figured
out I was misspelling the Form name and the other that I got the code to work
by changing it from the form object to a module, and asking another question
about the code. But now I got your reply. I understand your explanation, and
I'll try using that code instead.

Concerning your comment:

"if you call using 3/25/2006, you still get 3/24/2006. I don't know if this
is correct or not. Let me know if this is an issue so we can get that working
properly."

Selecting 3/25/2006 as a processing date would be extremely rare if not
absolutely not a possibility. That would be the Start date of the next period
(April). The most likely scenario is that a user would select a processing
date sometime in April before the April close date. Since all the data is
kept in a database table, what I do is filter those dates to make sure that I
am processing only transactions that fall between the start and closing
dates. The effective date of the processing is the closing date of each
period. Financial periods close on the last friday of each month with a few
exceptions that are determined before the beginning of the new fiscal year.
Since they are almost always a couple of months that will close on the next
to last instead of the last friday, I want to have an easy way of making a
one-time easy selection of those exceptions. Before now we would make a new
table each year with the opening and closing dates. I know that maybe this is
a more complicated solution, but it seems more elegant to me, and I can
conceive of an easier way to make the one-time selections of the annual
exceptions. It also helps me learn more about coding. Thanks so very much for
your help. I really appreaciate it.

 

[Guest]

You are correct, the argument is not optional, so you must pass a valid date
or you will get an error. As I stated in my previous post, it is an error to
set the varTheDate variable within the function. It gets its value from the
calling procedure, so when you call it using your control on the form:

GetCloseDate(Forms!frm_Journal_ID.txtInvDate)

The argument varTheDate is already set to the value in
Forms!frm_Journal_ID.txtInvDate.

The issue of it being in a module is not important. It could be in the
General section of your form module and still work the same way. The reason
to put it in a standard module is that it can then be used by other
procedures in your application. What I do is group my Public functions and
Subs by what they do. For example, I have the original GetLastFriday, both
of the new functions, and many other date handling routines in a module
called modDateFunctions. That way, if I need to manipulate a date, I can go
to that module and see if I already have something that will do what I want.

What about the problem I addressed in my last post in that if you use a date
in a month that is after the Close date, it will use that month rather than
the period the date is actually in. for example, for March, 2006, if you
enter any date from 3/25/2006 through 3/31/2006, it returns 3/24/2006 as the
close date and 2/24/2006 for the start date, which may not be what you want.
If we need to fix this, let me know so I can help come up with an idea that
will solve that problem.

 

[Guest]

I did reply to your other question, but it's up above your reply because I
didn't wait for your reply before posting it...

Selecting 3/25/2006 as a processing date would be extremely rare if not
absolutely not a possibility. That would be the Start date of the next period
(April). The most likely scenario is that a user would select a processing
date sometime in April before the April close date. Since all the data is
kept in a database table, what I do is filter those dates to make sure that I
am processing only transactions that fall between the start and closing
dates. The effective date of the processing is the closing date of each
period. Financial periods close on the last friday of each month with a few
exceptions that are determined before the beginning of the new fiscal year.
Since they are almost always a couple of months that will close on the next
to last instead of the last friday, I want to have an easy way of making a
one-time easy selection of those exceptions. Before now we would make a new
table each year with the opening and closing dates. I know that maybe this is
a more complicated solution, but it seems more elegant to me, and I can
conceive of an easier way to make the one-time selections of the annual
exceptions. It also helps me learn more about coding. Thanks so very much for
your help. I really appreaciate it.

 

[Guest]

Glad to be of assistance, Ray. I was flattered by your original post
crediting me with assisting you with the LastFriday function.

If the months of the exceptions will change from year to year, then rather
than having to modifiy the code each year. It may make sense to create a
small two column table that you could retrieve the exception months from.
One column would be the year, and the other the exception month. So, for the
current situation, you would have 2 records 2006 / 3 and 2006 /9

To get that value you could just use a DLookup

=DLookup("[ExMonth]", "tblExcept", "[ExMonth] = " & Month(Date) _
& " And [ExYear] = " & Year(Date))

If it returns Null, then it is not an exception. If it returns a value, it is.

Best of Luck to you.

 

[Guest]

That sounds exactly like what I'd like to do. How would I hook this up to the
other functions?

Glad to be of assistance, Ray. I was flattered by your original post
crediting me with assisting you with the LastFriday function.

If the months of the exceptions will change from year to year, then rather
than having to modifiy the code each year. It may make sense to create a
small two column table that you could retrieve the exception months from.
One column would be the year, and the other the exception month. So, for the
current situation, you would have 2 records 2006 / 3 and 2006 /9

To get that value you could just use a DLookup

=DLookup("[ExMonth]", "tblExcept", "[ExMonth] = " & Month(Date) _
& " And [ExYear] = " & Year(Date))

If it returns Null, then it is not an exception. If it returns a value, it is.

Best of Luck to you.

I did reply to your other question, but it's up above your reply because I
didn't wait for your reply before posting it...

Selecting 3/25/2006 as a processing date would be extremely rare if not
absolutely not a possibility. That would be the Start date of the next period
(April). The most likely scenario is that a user would select a processing
date sometime in April before the April close date. Since all the data is
kept in a database table, what I do is filter those dates to make sure that I
am processing only transactions that fall between the start and closing
dates. The effective date of the processing is the closing date of each
period. Financial periods close on the last friday of each month with a few
exceptions that are determined before the beginning of the new fiscal year.
Since they are almost always a couple of months that will close on the next
to last instead of the last friday, I want to have an easy way of making a
one-time easy selection of those exceptions. Before now we would make a new
table each year with the opening and closing dates. I know that maybe this is
a more complicated solution, but it seems more elegant to me, and I can
conceive of an easier way to make the one-time selections of the annual
exceptions. It also helps me learn more about coding. Thanks so very much for
your help. I really appreaciate it.

 

[Guest]

Here's exactly how it works! This uses a table named tblExcept. It has two
fields, ExMonth and ExYear, both are Long Integer. For this year, you need
two records, 2006 / 3 and 2006 / 9

Here is how it works. Notice we no longer need lngException. We don't even
care what the month number is. All we are concerned with is whether we find
a record in tblExcept. If there is no record that matches the month of the
date, then the DLookup returns Null, so it is not an exception. If it
returns any other value than Null, it is an exception.

Function GetStartDate(varTheDate As Date) As Date
Dim dtmStart As Date

dtmStart = DateAdd("d", 1, LastFriday(DateAdd("m", -1,
LastFriday(varTheDate))))
If Not IsNull(DLookup("[ExMonth]", "tblExcept", "[ExYear] = " _
& year(dtmStart) & " And [ExMonth] = " & Month(dtmStart))) Then
dtmStart = DateAdd("ww", -1, dtmStart)
End If
GetStartDate = dtmStart
End Function

Function GetCloseDate(varTheDate As Date) As Date
Dim dtmClose As Date

dtmClose = LastFriday(varTheDate)
If Not IsNull(DLookup("[ExMonth]", "tblExcept", "[ExYear] = " _
& year(dtmClose) & " And [ExMonth] = " & Month(dtmClose))) Then
dtmClose = DateAdd("ww", -1, dtmClose)
End If
GetCloseDate = dtmClose
End Function

That sounds exactly like what I'd like to do. How would I hook this up to the
other functions?

Glad to be of assistance, Ray. I was flattered by your original post
crediting me with assisting you with the LastFriday function.

If the months of the exceptions will change from year to year, then rather
than having to modifiy the code each year. It may make sense to create a
small two column table that you could retrieve the exception months from.
One column would be the year, and the other the exception month. So, for the
current situation, you would have 2 records 2006 / 3 and 2006 /9

To get that value you could just use a DLookup

=DLookup("[ExMonth]", "tblExcept", "[ExMonth] = " & Month(Date) _
& " And [ExYear] = " & Year(Date))

If it returns Null, then it is not an exception. If it returns a value, it is.

Best of Luck to you.

I did reply to your other question, but it's up above your reply because I
didn't wait for your reply before posting it...

Selecting 3/25/2006 as a processing date would be extremely rare if not
absolutely not a possibility. That would be the Start date of the next period
(April). The most likely scenario is that a user would select a processing
date sometime in April before the April close date. Since all the data is
kept in a database table, what I do is filter those dates to make sure that I
am processing only transactions that fall between the start and closing
dates. The effective date of the processing is the closing date of each
period. Financial periods close on the last friday of each month with a few
exceptions that are determined before the beginning of the new fiscal year.
Since they are almost always a couple of months that will close on the next
to last instead of the last friday, I want to have an easy way of making a
one-time easy selection of those exceptions. Before now we would make a new
table each year with the opening and closing dates. I know that maybe this is
a more complicated solution, but it seems more elegant to me, and I can
conceive of an easier way to make the one-time selections of the annual
exceptions. It also helps me learn more about coding. Thanks so very much for
your help. I really appreaciate it.

 

[Guest]

I'm working with the functions, but there is a problem as you mentioned. When
the transaction date is chosen in the same month but after the closing date
then the closing date returned is still the same, which is wrong. The closing
date should be for the next month. For example, in the month of March, the
last Friday in 2006 is the 31st. This is one of the exception months, so the
close date for the period is actually the 24th. That means that the next
period begins on the next day. Any transaction dated March 25 should actually
be in the April fiscal month, and it's closing date is the last Friday in
April, or 4/28/2006. This is not what the function is doing.