Results 1 to 7 of 7

Thread: not a clue

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: not a clue

    Hello, below is my code for some simple math for my form. If it's works fine, but if the is a better way feel free to let me know. My question is related to the code. On my form I have the allotted value, which is on the sub form and the tblvac. On my tblEmployee's I have the hire date. What I would like to do is have the Allotted value update according to the hire date annually.

    Example:
    After 1 year of service you are allotted 40hrs of vacation, 2 years 80, 7 years 120 and 15 years 160. So If I were hired on 1-1-04 the value of course would be 0, but on 1-1-05 it would become 40. Probably possible but I don't have a clue.


    Private Sub EndDate_LostFocus()
    Days_Used.Value = EndDate - StartDate
    Hours_Used.Value = DaysUsed.Value * 10 'ten hour shifts
    Remaining.Value = Allotted.Value - Hours_Used.Value
    End Sub

  2. #2
    Join Date
    Jan 2004
    Posts
    9
    With the suggestion of using a query I tried the following. Number 1 works Number 2 does not. Can anyone help?


    1)Earned Vac Hrs: IIf([DaysEmployed]>2554,"80") 'this works fine.

    2)Earned Vac Hrs: IIf([DaysEmployed]<=4596,"80") Or IIf([DaysEmployed]>=4597,"120") 'this returns a -1....why

    The way I understand it is; if DaysEmployes is equal or less than 4596 return 80 (works fine) if days employed is equal or greater than 4597 return 120 (does not work)

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Not sure but try:

    IIf([DaysEmployed]<=4596,"80", IIf([DaysEmployed]>=4597,"120"))

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    Talking

    Thanks a million, that works.

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    I ran into a snag, the above was a sample of what I needed to do and it works great, until I add the rest of the expression. Can you please look at this and tell me if I am doing this wrong.

    IIf([DaysEmployed]>=366,40,IIf([DaysEmployed]>=730,80,IIf([DaysEmployed]>=2555,120,IIf([DaysEmployed]>=5475,160))))

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Code:
    IIf([DaysEmployed]>=366,40, IIf([DaysEmployed]>=730,80, _
    IIf([DaysEmployed]>=2555,120,IIf([DaysEmployed]>=5475,160))))
    You have to remember, the If or IIf statement is used for checking conditions. IIf is the same as using If-Then-Else. By looking at your code line I can quickly see that you will always get 40 as the result. Nested IIF's will stop the checking once the first condition that is true has been found, which in this case [DaysEmployed] >= 366 (If DaysEmployed is greater than or equal to 366). All the other [DaysEmployed] you are checking, are obviously Greater Than 366. Therefore, more conditions need to be supplied. Please keep in mind though, nested IIF's is really not a good way to go. It's very hard to read through. I'll show you the IIF with further conditions then I'll show you some other code that can accomplish the same thing but need to be in a module.

    Code:
    'ALL ONE LINE.
    IIf(([DaysEmployed]>=366 AND [DaysEmployed]<730), 40, IIf(([DaysEmployed]>=730 AND _
    [DaysEmploed]<2555), 80, IIf(([DaysEmployed]>=2555 AND _
    [DaysEmployed]<5475), 120, IIf([DaysEmployed]>=5475,160))))
    Other Ways............
    Code:
    Dim Result As Integer
    If Me.DaysEmployed >= 366 And Me.DaysEmplyed < 730 Then
       Result = 40
    ElseIf Me.DaysEmployed >= 730 And Me.DaysEmplyed < 2555 Then
       Result = 80
    ElseIf Me.DaysEmployed >= 2555 And Me.DaysEmplyed < 5475 Then
       Result = 120
    ElseIf Me.DaysEmployed >= 5475 Then
       Result = 160
    End If
    ************* OR *************

    Code:
    Dim Result As Integer
    Select Case Me.DaysEmployed
        Case >= 366
             Result = 40
        Case >= 730
             Result = 80
        Case >= 2555
             Result = 120
        Case >= 5475
            Result = 160
    End Select
    And there are more ways yet. Of course in these other two examples I am getting the DaysEmployed from a Form field. Normaly, this code would go into a Function procedure and could be called from the OnCurrent event of your form. For example:

    Code:
    Private Function CalculateVacationHours(DaysOfService As Long) As Long
         Select Case DaysOfService
           Case >= 366
                CalculateVacationHours = 40
           Case >= 730
                CalculateVacationHours = 80
           Case >= 2555
                CalculateVacationHours = 120
           Case >= 5475
               CalculateVacationHours = 160
           Case Else
               CalculateVacationHours = 0
       End Select
    End Function
    To Use this Function, call it from the OnCurrent event of your Form. For example:

    Code:
    Private Sub Form_Current()
        'myFormField is the form field (textbox) that will display the result.
        'DaysEmployed is the form field that contains the number of days worked.
        Me.myFormField = CalculateVacationHours(CLng(Me.DaysEmployed))
    End Sub
    Hope this helps....
    Last edited by CyberLynx; 01-20-04 at 01:30.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just nest your IIFs in reverse sequence and bob's your uncle

    IIf([DaysEmployed] >= 5475 ,160 ,
    IIf([DaysEmployed] >= 2555 ,120 ,
    IIf([DaysEmployed] >= 730 ,80 ,
    IIf([DaysEmployed] >= 366 ,40 ))))

    that's not too hard to read, is it?

    of course, if you have to code this left, right, and centre, then perhaps a module might be better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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