# Thread: Days Calculation (2000 SR-1)

1. ## Days Calculation (2000 SR-1)

﻿I am trying to Automate some data entry. My thought was to use an AfterUpdate Event to
Calculate the number of days in a rental period based on the entry of the [BeginDate]. If the
[BeginDate] is during the month, the number of days = the number of days in the month, i.e. Jan
= 31, Feb = 28 except leap year, Mar = 31 etc.

However if the [BeginDate] is the last day of the month, I need the Number of Days to = the days
in the next month.

Date Number of Days
Jan 31- 28 except leap year then 29
Feb 28 or 29 - 31
Mar 31- 30
Apr 30 - 31
May 31- 30
Jun 30 - 31
Jul 31- 31
Aug 31- 30
Sep 30 - 31
Oct 31- 30
Nov 30 - 31
Dec 31 - 31

2. ## Re: Days Calculation (2000 SR-1)

The following function is based on an example from the NeatCode sample database provided by Microsoft:

Function DaysInMonth2(d)
' Returns the number of days in a month
' Requires a date argument, since February can change if it's a leap year
' Lets Access figure it out
If VarType(d) <> vbDate Then
DaysInMonth2 = Null
Else
DaysInMonth2 = DateSerial(Year(d), Month(d) + 1, 1) - DateSerial(Year(d), Month(d), 1)
End If
End Function

DaysInMonth2([BeginDate] + 1) returns the number of days in the month containing the day after BeginDate. So for any day but the last in a month, it returns the number of days in that month; for the last day in a month, it returns the number of days in the next month.

3. ## Re: Days Calculation (2000 SR-1)

The function provided sounds as if it is just what I am looking for. I have set up a Public Function DaysInMonth2(d) in my Utility Module.

I not clear exactly the best way to call the function, once the [BeginDate] is entered.

4. ## Re: Days Calculation (2000 SR-1)

Put a text box on your form.

In the Properties window, enter the following in the Control Source property:

=DaysInMonth2([BeginDate]+1)

The value of the text box will be recalculated automatically when the user enters or modifies BeginDate.

5. ## Re: Days Calculation (2000 SR-1)

Hans,
Thanks for all your assistance but I am still confused. Current the input form uses has a field [NumberOfDays]. Does this need to be changed to a text box? I need the number of Days to end up in the [NumberofDays] field since it is used in many calculations.

I have 2 fields that are used to determine the number of days.
[PayCodeID]
1= Weekly
2=Monthly
3=Daily
4-NonRoom
and
[RentBeginDate]. The answer need to be put in [NumberOfDays]

I need to have a condition to test when [PayIDCode] = 2 then it would use the DaysInMonth2 function to calculate the [NumberOfDays]. If the [PayCodeID] <> 2 then then the operator would enter the number of days. I have an AfterUpdate event in the [PayCodeID] property that puts a zero in [NumberofDays] if the [PayCodeID] =4. (The event also puts in the [RoomRate] based on a combination of [PayCodeID] and [RoomType].)

Tom

6. ## Re: Days Calculation (2000 SR-1)

Tom,

Thanks for the explanation. I hadn't understood what you were trying to do before.

You will need code in the AfterUpdate event of PayCodeID and of RentBeginDate. Of course, you'll need to add your own code for other things to be done.

Private Sub PayCodeID_AfterUpdate()
Select Case [PayCodeID]
Case 1, 3
MsgBox "Enter number of days", vbInformation
[NumberOfDays].SetFocus
Case 2
[NumberOfDays] = DaysInMonth2([RentBeginDate] + 1)
Case 4
[NumberOfDays] = 0
End Select
End Sub

Private Sub RentBeginDate_AfterUpdate()
If [PayCodeID] = 2 Then
[NumberOfDays] = DaysInMonth2([RentBeginDate] + 1)
End If
End Sub

If PayCodeID = 2, is the operator allowed to change the number of days?

If not, you can add code to lock/unlock NumberOfDays in the AfterUpdate event of PayCodeID and in the OnCurrent event of the form.

7. ## Re: Days Calculation (2000 SR-1)

I hate to be a pest but I still can't make it work. Here's what I got. I was only trying to make the function work for standard -1.

Private Sub PayCodeID_AfterUpdate()
Select Case Me!RoomTypeID
Case 1 'Standard - 1
If PayCodeID = 1 Then Me!RoomRate = 199
If PayCodeID = 2 Then Me!RoomRate = 799
If PayCodeID = 2 Then Me!NumberofDays = DaysInMonth2([RentBegDate] + 1)
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!NumberofDays = 0
If PayCodeID = 4 Then Me!RoomRate = 0
Case 2 'Standard King - 2
If PayCodeID = 1 Then Me!RoomRate = 0 Else
If PayCodeID = 2 Then Me!RoomRate = 0
If PayCodeID = 3 Then Me!RoomRate = 0
If PayCodeID = 4 Then Me!RoomRate = 0
Case 3 'Mini - 1
If PayCodeID = 1 Then Me!RoomRate = 169 Else
If PayCodeID = 2 Then Me!RoomRate = 0
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!RoomRate = 0
Case 4 'Double - 1
If PayCodeID = 1 Then Me!RoomRate = 229 Else
If PayCodeID = 2 Then Me!RoomRate = 899
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!RoomRate = 0
Case 5 'Standard - 2
If PayCodeID = 1 Then Me!RoomRate = 219 Else
If PayCodeID = 2 Then Me!RoomRate = 859
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
Case 6 'Mini - 2
If PayCodeID = 1 Then Me!RoomRate = 189 Else
If PayCodeID = 2 Then Me!RoomRate = 0
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
Case 7 'Standard King - 1
If PayCodeID = 1 Then Me!RoomRate = 0 Else
If PayCodeID = 2 Then Me!RoomRate = 0
If PayCodeID = 3 Then Me!RoomRate = 0
If PayCodeID = 4 Then Me!RoomRate = 0
Case 8 'Executive - 1
If PayCodeID = 1 Then Me!RoomRate = 239 Else
If PayCodeID = 2 Then Me!RoomRate = 939
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!RoomRate = 0
Case 9 'Jr. Executive - 1
If PayCodeID = 1 Then Me!RoomRate = 189 Else
If PayCodeID = 2 Then Me!RoomRate = 739
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!RoomRate = 0
Case 10 ' Double - 2
If PayCodeID = 1 Then Me!RoomRate = 249 Else
If PayCodeID = 2 Then Me!RoomRate = 959
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
Case 11 'Jr. Executive - 2
If PayCodeID = 1 Then Me!RoomRate = 209 Else
If PayCodeID = 2 Then Me!RoomRate = 799
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
Case 12 'Executive - 2
If PayCodeID = 1 Then Me!RoomRate = 259 Else
If PayCodeID = 2 Then Me!RoomRate = 999
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
Case 13 'Handicap - 1
If PayCodeID = 1 Then Me!RoomRate = 199 Else
If PayCodeID = 2 Then Me!RoomRate = 799
If PayCodeID = 3 Then Me!RoomRate = 33
If PayCodeID = 4 Then Me!RoomRate = 0
Case 14 'Handicap - 2
If PayCodeID = 1 Then Me!RoomRate = 219 Else
If PayCodeID = 2 Then Me!RoomRate = 859
If PayCodeID = 3 Then Me!RoomRate = 44
If PayCodeID = 4 Then Me!RoomRate = 0
End Select
End Sub

Any suggestions?

Tom

8. ## Re: Days Calculation (2000 SR-1)

Hans,

﻿The DaysInMonth2 function is working; however, there is still 1 small problem.

The tab order of the fields are:
RoomTypeID
PayCodeID
RoomRate
RentBegDate
NumberOfDays

After entering the RoomTypeID and PayCodeID (Using drop down menus), the code properly
enters the room rate. After entering the RentBegDate manually, you get an error message

9. ## Re: Days Calculation (2000 SR-1)

﻿I think I found the solution. Initial testing seems OK. I changed the
RentBeginDate_AfterUpdate() event procedure to a NumberofDays_OnGotFocus() and also
deleted the portion of PayCodeID_AfterUpdate() referencing DaysInMonth2 function.

Many thanks for pointing me in the right direction.

Tom

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•