Results 1 to 9 of 9
Thread: Days Calculation (2000 SR1)

20020614, 12:38 #1
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Days Calculation (2000 SR1)
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

20020614, 12:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Days Calculation (2000 SR1)
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.

20020614, 13:55 #3
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Days Calculation (2000 SR1)
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.

20020614, 14:03 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Days Calculation (2000 SR1)
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.

20020614, 14:43 #5
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Days Calculation (2000 SR1)
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
4NonRoom
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].)
Thanks again for your assistance.
Tom

20020614, 15:11 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Days Calculation (2000 SR1)
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.

20020614, 16:51 #7
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Days Calculation (2000 SR1)
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

20020614, 20:48 #8
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Days Calculation (2000 SR1)
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

20020615, 15:42 #9
 Join Date
 May 2002
 Location
 Roanoke, Virginia, USA
 Posts
 493
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Days Calculation (2000 SR1)
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