need help on IIf

[Fitz]

The following IIf statment =IIf([Total Hours]<=26.4,[Total Hours]*[Daily]/8) is what i have in a report and works. However i want to continue this IIf. Can you help me with the following IIF =IIf([Total Hours]<=26.4,[Total Hours]*[Daily]/8),IIf([Total Hours]<=40,[Daily]*3,IIf([Total Hours]<=119,[Total Hours]*[Weekly]/40,IIf([Total Hours]<=176,[Monthly]*3,IIf([Total Hours]>=177,[Total Hours]*[Monthly]/176)))))

javascript:emoticon('')
Help

 

[jobb]

looks like you have your first closed parenthesis too soon

try this maybe?

=If([Total Hours]<=26.4,[Total Hours]*[Daily]/8,IIf([Total Hours]<=40,[Daily]*3,IIf([Total Hours]<=119,[Total Hours]*[Weekly]/40,IIf([Total Hours]<=176,[Monthly]*3,IIf([Total Hours]>=177,[Total Hours]*[Monthly]/176)))))

 

[Corticus]

Hi Fitz,

Might I suggest a UDF?

Go the VB editor, insert a module, and paste in code like this:

Function FXhours(FXtotal, FXmonthly, FXweekly, FXdaily)

Select Case FXtotal
    Case Is <= 26.4
        FXhours = FXtotal * FXdaily / 8
    Case Is <= 40
        FXhours = FXdaily * 3
    Case Is <= 119
        FXhours = FXtotal * FXweekly / 40
    Case Is <= 176
        FXhours = FXmonthly * 3
    Case Is >= 177
        FXhours = FXtotal * FXmonthly / 176
End Select

End Function

You will use this function like any standard Access function, like in your report:
=FXHours([TxtTotal],[TxtMonthly],[TxtWeekly],[TxtDaily])

The arguments will be controls that house these respective values (TxtTotal would hold Total Hours).

You could probably modify your original function to do what you need, but this just seems so much clearer to me...

Adjust the arguments as necessary for your needs.

Also, avoid naming anything with spaces, that will invariably cause problems down the road.


HTH,

 

[Fitz]

Thanks Jobb and Corticus
,
I 'am a newbie I see my mistake in the formula.

I would like to use the UDF, I can type it in but I have no idea how to apply in my report.

Corticus could you give me an example

Thanks

 

[Corticus]

Sure Fitz,

Once you get the hang of using a UDF, you will love them (at least I do).

Insert the code in a standard module (alt+F11, insert|module, double click on the new module and insert paste in the code)

What you've done is make yourself a new formula, called FXhours(). It has four arguments, Total Hours, monthly, weekly, and daily.

If you use this in a report, you would use it like:
=FXHours([Total Hours], [monthly], [weekly], and [daily]). You would refer to these fields just as you were attempting to in your original formula.

You would replace [Total Hours] with the field in the report or query that contained this data.

To simplify, say you made a UDF (user defined function) that had two arguments. The function is Adder(arg1, arg2).

You might define it like:

Function Adder(arg1, arg2)
Adder = arg1 + arg2
End Function

And you would use the function like:
Adder(1,5) which would result in 6
or
Adder([field1],[field2]) which would add the data in field1 plus field2
or
Adder([txt1],[txt2]) which would add the number in Txt1 to Txt2.

I'll work it into a db if you need, just send it to
corticus@theofficeexperts.com (all data is confidential)

Its hard to provide a good sample for you without seeing how you intend to use it, but I'm pretty sure this UDF will get the job done for you.

HTH,