Nested IIF help

[Incolor]

Hi All,

I have the following statement in a query.

8HRTWA:
CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15,IIf([LIMIT_TYPE]
In ("TLV","PEL") And [TIME_MINUTES]>15,480,IIf([LIMIT_TYPE]="REL" And
[TIME_MINUTES]>15,600,IIf([LIMIT_TYPE]="CEILING" And
[TIME_MINUTES]>15,nz([RESULT])))))))

It is sort of working. It is still including the [time_minutes] that
are less than 15 where I state that the [time_minutes] should be >15.
Any idea what I am doing wrong?

This is what the statement should be doing:
If the [time_minutes] is less than 15 the formula is result*time
minutes/15

If the [time_minutes] is >15 AND the limit type is TLV or PEL the
formula is result*time minutes/480

If the [time_minutes] is >15 AND the limit type is REL the formula is
result*time minutes/600

If the [time_minutes] is >15 AND the limit type is CEILING then there
is no formula; 8HRTWA is the Result.

I hope it makes sense. Any guidance would be greatly appreciated!

MV

 

[Duane Hookom]

Have you considered creating a small user-defined function that accepts a
couple arguments and returns the appropriate value? I would hate to be the
person who had to modify these type of expressions WHEN someone changes the
15, 480, 600, ... It's not IF it happens but WHEN. I you create a small
module modBusinessCalcs you will be able to easily find and modify your
calc.

 

[Incolor]

Duane;

These are actual standards for a short exposure(15), 8 hour exposure
(480) and 10 hr exposure (600). So they will not change.

I am not familiar with creating user defined functions, I am new to
VBA; I tried one, but now sure where it would go etc.

MV

Have you considered creating a small user-defined function that accepts a
couple arguments and returns the appropriate value? I would hate to be the
person who had to modify these type of expressions WHEN someone changes the
15, 480, 600, ... It's not IF it happens but WHEN. I you create a small
module modBusinessCalcs you will be able to easily find and modify your
calc.

--
Duane Hookom
MS Access MVP

Hi All,

I have the following statement in a query.

8HRTWA:
CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15,IIf([LIMIT_TYPE]
In ("TLV","PEL") And [TIME_MINUTES]>15,480,IIf([LIMIT_TYPE]="REL" And
[TIME_MINUTES]>15,600,IIf([LIMIT_TYPE]="CEILING" And
[TIME_MINUTES]>15,nz([RESULT])))))))

It is sort of working. It is still including the [time_minutes] that
are less than 15 where I state that the [time_minutes] should be >15.
Any idea what I am doing wrong?

This is what the statement should be doing:
If the [time_minutes] is less than 15 the formula is result*time
minutes/15

If the [time_minutes] is >15 AND the limit type is TLV or PEL the
formula is result*time minutes/480

If the [time_minutes] is >15 AND the limit type is REL the formula is
result*time minutes/600

If the [time_minutes] is >15 AND the limit type is CEILING then there
is no formula; 8HRTWA is the Result.

I hope it makes sense. Any guidance would be greatly appreciated!

MV

 

[Duane Hookom]

You open a new blank module and then save it at "modBusinessCalcs". Then add
your code like:

Function GetHRTWA(dblResult as Double, dblTimeMinutes as Double, _
strLimitType as String) as Double
Dim dblResXMinutes as Double
Dim dblReturn as Double
dblResXMinutes = dblResult * dblTimeMinutes
If dblTimeMinutes <=15 Then
dblReturn = dblResXMinutes/15
End If
' other code....
GetHRTWA = dblReturn
End Function


--
Duane Hookom
MS Access MVP

Duane;

These are actual standards for a short exposure(15), 8 hour exposure
(480) and 10 hr exposure (600). So they will not change.

I am not familiar with creating user defined functions, I am new to
VBA; I tried one, but now sure where it would go etc.

MV

Have you considered creating a small user-defined function that accepts a
couple arguments and returns the appropriate value? I would hate to be
the
person who had to modify these type of expressions WHEN someone changes
the
15, 480, 600, ... It's not IF it happens but WHEN. I you create a small
module modBusinessCalcs you will be able to easily find and modify your
calc.

--
Duane Hookom
MS Access MVP

Hi All,

I have the following statement in a query.

8HRTWA:
CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15,IIf([LIMIT_TYPE]
In ("TLV","PEL") And [TIME_MINUTES]>15,480,IIf([LIMIT_TYPE]="REL" And
[TIME_MINUTES]>15,600,IIf([LIMIT_TYPE]="CEILING" And
[TIME_MINUTES]>15,nz([RESULT])))))))

It is sort of working. It is still including the [time_minutes] that
are less than 15 where I state that the [time_minutes] should be >15.
Any idea what I am doing wrong?

This is what the statement should be doing:
If the [time_minutes] is less than 15 the formula is result*time
minutes/15

If the [time_minutes] is >15 AND the limit type is TLV or PEL the
formula is result*time minutes/480

If the [time_minutes] is >15 AND the limit type is REL the formula is
result*time minutes/600

If the [time_minutes] is >15 AND the limit type is CEILING then there
is no formula; 8HRTWA is the Result.

I hope it makes sense. Any guidance would be greatly appreciated!

MV