# Thread: not a clue

1. Registered User
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. Registered User
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. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
Not sure but try:

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

4. Registered User
Join Date
Jan 2004
Posts
9
Thanks a million, that works.

5. Registered User
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. Stuck on my opinions...
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 00:30.

7. SQL Consultant
Join Date
Apr 2002
Location
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

#### Posting Permissions

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