convert the excel formula into a Access 2010 expression

[Cayetano]

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"))))))))

[ADezii]

=IIf(I8 > 1.88, "8", IIf(I8 > 1.63, "7", IIf(I8 > 1.38, "6", _
     IIf(I8 > 1.08, "5", IIf(I8 > 0.88, "4", IIf(I8 > 0.63, 3, _
     IIf(I8 > 0.38, 2, IIf(I8 > 0.12, "1", "0"))))))))

[Cayetano]

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?

[ADezii]

Post all the SQL that you have so far.

[akselo]

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:

Function group(minutes As Double) As Integer
 
Select Case minutes
    Case 0.13 To 0.37
        group = 1
    Case 0.3700001 To 0.62
        group = 2
    Case 0.6200001 To 0.87
        group = 3
    Case 0.8700001 To 1.07
        group = 4
    Case 1.0700001 To 1.37
        group = 5
    Case 1.3700001 To 1.62
        group = 6
    Case 1.6200001 To 1.87
        group = 7
    Case 1.8700001 To 2.13
        group = 8
 
End Select
End Function
 

This function can then be called from the update query interface like so:

update tblSource set TotalUnits=group(time);
 

[Rabbit]

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

[ADezii]

@Rabbit - Very interesting approach, what is the logic behind it?

[Rabbit]

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.

[ADezii]

Bear with me, Rabbit. Don't the following Unit Blocks negate the assumption?

'0.88 minutes through 1.07 minutes = 4 units - {19 Minutes}
'1.08 minutes through 1.37 minutes = 5 units - {29 Minutes}

[Rabbit]

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.

[ADezii]

Thanks for the explanation, Rabbit.