Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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].)

    Thanks again for your assistance.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •