UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Determine If A Date Is A Quarterly Anniversary, Any Version    
 
   
bmacbride
post Dec 26 2017, 04:21 PM
Post#1



Posts: 3
Joined: 26-December 17



I have a db that needs to add fees for each person based on their billing StartDate.

For those with quarterly billing, I need to determine if today is the date to bill them.

For example if their StartDate 3/31/15 and today's date is 6/30/17 then I add fees.

I have played with DateDiif and DateSerial, but I am not having any luck.
Go to the top of the page
 
theDBguy
post Dec 26 2017, 04:43 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,488
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

You might be able to use the DateDiff() function in conjunction with the Mod operator to determine if a fee needs to be added. For example:

If DateDiff("m",[StartDate],Date()) Mod 3 = 0 Then
'add fee
Else
'no fee
End If

Hope it helps...
Go to the top of the page
 
bmacbride
post Dec 26 2017, 10:59 PM
Post#3



Posts: 3
Joined: 26-December 17



I've come up with this, but is does not work for 3/31/17. This should get billed on 6/30/17 as there is no 6/31/17.

Public Function IsQuarterly(BillingDate, TodaysDate As Date) As String
On Error GoTo Err_Handler

Dim YY As Integer
Dim MM As Integer
Dim DD As Long

MM = DateDiff("m", BillingDate, TodaysDate)
DD = DateDiff("d", DateAdd("m", MM, BillingDate), TodaysDate)

' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, BillingDate), TodaysDate)
End If

YY = MM \ 12 ' integer division.
MM = MM Mod 12 ' remainder.


'need to tet months /3 and see if there is a remainder
If MM Mod 3 = 0 And DD = 0 Then
IsQuarterly = True
Else
IsQuarterly = False
End If

Exit Function

Err_Handler:
MsgBox Err.Description
Exit Function
Go to the top of the page
 
bmacbride
post Dec 26 2017, 11:00 PM
Post#4



Posts: 3
Joined: 26-December 17



Thanks, that points me in the right direction.
Go to the top of the page
 
BruceM
post Dec 27 2017, 09:10 AM
Post#5


UtterAccess VIP
Posts: 7,678
Joined: 24-May 10
From: Downeast Maine


A few things:
QUOTE
I've come up with this, but is does not work for 3/31/17. This should get billed on 6/30/17 as there is no 6/31/17.

How are you generating a value that becomes 6/31/17? I don't see that in the function you posted.

There is no need to pass TodaysDate to the function. Just use the built-in Date function whenever you need the current date.

If the intent of the function is to set a value of either True or False for IsQuarterly, have the function return a Boolean rather than a string:
Public Function IsQuarterly(BillingDate As Date) As Boolean

Note that if you do BillingDate, TodaysDate as Date (or something like Dim StartDate, EndDate As Date) the first one (BillingDate or StartDate in these two examples) will be a variant, which is the default. You need to do BillingDate As Date, TodaysDate as Date (or Dim StartDate As Date, EndDate As Date). But as mentioned, there is no need to pass today's date.

If you add some number of months to the last day of the month, it will be the last day of the resulting month if that month is shorter than the first month. For instance, DateAdd("m",2,#12/31/2017#) will return 2/28/2018. However, DateAdd("m",2,#11/30/2017#) will return 1/30/2018. Even though there are 31 days in January, January 30 is a real date, so DateAdd returns that date since it is possible. If you need the last day of any month you can use DateSerial. The last day of the current month would be DateSerial(Year(Date()),Month(Date()) + 1,0). I don't know if this is of interest to the current situation, but there it is.

What happens if the billing date falls on a weekend or holiday? It looks to me as if you are concerned only with the exact quarterly date. If their due date falls on a day the office is closed, by the next day IsQuarterly will show False. Maybe you could explain in a little more detail just what you are trying to do.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 10:50 AM