469,282 Members | 2,134 Online

# convert the excel formula into a Access 2010 expression 3
I created a table that has a field name InTime and a field name OutTime with a calculated field name TotalTime. Now I need to create a calculated field name TotalUnits.
I need to convert the excel formula into a Access expression. Im including the numbers of minutes that equal to a unit (if this helps).

Number of Minutes in Military Time Units
0.13 minutes through 0.37 minutes = 1 unit
0.38 minutes through 0.62 minutes = 2 units
0.63 minutes through 0.87 minutes = 3 units
0.88 minutes through 1.07 minutes = 4 units
1.08 minutes through 1.37 minutes = 5 units
1.38 minutes through 1.62 minutes = 6 units
1.63 minutes through 1.87 minutes = 7 units
1.88 minutes through 2.13 minutes = 8 units

This is the excel formual:
=IF(I8>1.88,"8",IF(I8>1.63,"7",IF(I8>1.38,"6",IF(I 8>1.13,"5",IF(I8>0.88,"4",IF(I8>0.63,"3",IF(I8>0.3 8,"2",IF(I8>0.12,"1","0"))))))))
Dec 20 '10 #1

If I am understanding the math correctly, can't you just use units = Int((minutes - 0.13) / 0.24) + 1

10 4768 8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
1. =IIf(I8 > 1.88, "8", IIf(I8 > 1.63, "7", IIf(I8 > 1.38, "6", _
2.      IIf(I8 > 1.08, "5", IIf(I8 > 0.88, "4", IIf(I8 > 0.63, 3, _
3.      IIf(I8 > 0.38, 2, IIf(I8 > 0.12, "1", "0"))))))))
Dec 21 '10 #2
Cayetano
3 Thank you for your quick response, I’ve tried the expression with no results. I keep getting the following error: The expression could not be saved because its result type, such as binary or NULL, is not supported by the server. I have no idea what that means.

I did change the [I8] to the field name TotalTime but it didn’t work. Am I doing something wrong?
Dec 21 '10 #3
8,800 Expert 8TB
Post all the SQL that you have so far.
Dec 21 '10 #4
akselo
21 To see if I understand--you are running an update query in access, or are you working in excel with linked data?

If you are working in access, you can use the IIF function in a nested way to account for each group (be sure to include the name of the field name in access and not the cell reference from excel), but I often find it is easier to insert a module and go with the select case statement , like so:

Expand|Select|Wrap|Line Numbers
1. Function group(minutes As Double) As Integer
2.
3. Select Case minutes
4.     Case 0.13 To 0.37
5.         group = 1
6.     Case 0.3700001 To 0.62
7.         group = 2
8.     Case 0.6200001 To 0.87
9.         group = 3
10.     Case 0.8700001 To 1.07
11.         group = 4
12.     Case 1.0700001 To 1.37
13.         group = 5
14.     Case 1.3700001 To 1.62
15.         group = 6
16.     Case 1.6200001 To 1.87
17.         group = 7
18.     Case 1.8700001 To 2.13
19.         group = 8
20.
21. End Select
22. End Function
23.
This function can then be called from the update query interface like so:
Expand|Select|Wrap|Line Numbers
1. update tblSource set TotalUnits=group(time);
2.
Dec 21 '10 #5
Rabbit
12,516 Expert Mod 8TB
If I am understanding the math correctly, can't you just use units = Int((minutes - 0.13) / 0.24) + 1
Dec 21 '10 #6
8,800 Expert 8TB
@Rabbit - Very interesting approach, what is the logic behind it?
Dec 22 '10 #7
Rabbit
12,516 Expert Mod 8TB
I had only taken a cursory glance at the description before coming up with my formulization. I had originally thought the numbers were discrete but it turns out that is not the case. The formula then, according to the Excel formula should be units = Int((time + 0.12) / 0.25) because each unit block is 0.25 minutes long starting at 0.13. Well... I say 0.13 even though the Excel formula says 0.12 because it seems to be a discrepancy with the rest of the formula. If it really does start with 0.12 then the formula doesn't work so I'm hoping that was a typo.

However, if the formula is correct, then it may result in negative numbers and numbers larger than 8, which may be something the OP doesn't want.
Dec 22 '10 #8
8,800 Expert 8TB
Bear with me, Rabbit. Don't the following Unit Blocks negate the assumption?
Expand|Select|Wrap|Line Numbers
1. '0.88 minutes through 1.07 minutes = 4 units - {19 Minutes}
2. '1.08 minutes through 1.37 minutes = 5 units - {29 Minutes}
Dec 22 '10 #9
Rabbit
12,516 Expert Mod 8TB
That's because he typed it wrong. In his formula, it's 1.13 and not 1.08. In that case it works. The only time it doesn't work is that in his formula, it starts with 0.12 and not 0.13. I'm hoping that too was a typo and he meant to start with 0.13.
Dec 22 '10 #10
8,800 Expert 8TB