My iif function has a hickup!!

[idavis1900]

I have a iif function that does work. But I would like it to do more. Here is my iff function

=([billAmount]-nz([ded],0))*IIf([ExpenseCategoryID]=3,0.5,IIf([ExpenseCategoryID]=7,"50.00",0.8))

This is the part not working right

([ExpenseCategoryID]=7,"50.00", I know why becasue I would like my answer to be $50.00. But becasue of the * in the begining of the statment it is timing the billamount by 50.

Any idea on how to change my iff function around.

Thank in advance.

Irene

 

[jamaarneen]

Hi Irene

I'm not sure what you want to achieve.
I assume that you have tree options:

- if ([ExpenseCategoryID]=3, you would like it to multiply =([billAmount]-nz([ded],0) by 0.5;
- if ([ExpenseCategoryID]=7,you would like the result to be 50.00 - NO multiplication at all;
- if ([ExpenseCategoryID] = other then 3 or 7, you would like it to multiply =([billAmount]-nz([ded],0) by 0.8;

if that's the case, you should try:

IIf([ExpenseCategoryID]=7,"50.00",IIf([ExpenseCategoryID]=3,]=([billAmount]-nz([ded],0))*0.5,]=([billAmount]-nz([ded],0))* 0.8))

Am I right? Did that helped?

 

[MajP]

You are almost always better off writing your own functions then using nested iifs. I can go on and on why iifs should be avoided and especially why nested ones should be avoided. Also I assume you want to return a currency not a string.

Public Function getBillAmount(billAmount As Variant, ExpCat As Variant, ded As Variant) As Currency
  Dim multiple As Single
  billAmount = Nz(billAmount, 0)
  ded = Nz(ded, 0)
  ExpCat = Nz(ExpCat, 0)

  Select Case ExpCat
    Case 3
     multiple = 0.5
    Case 7
      getBillAmount = 50
      Exit Function
    Case Else
      multiple = 0.8
  End Select
 
  getBillAmount = (billAmount - ded) * multiple
End Function

If you build a custom function. You can test it outside of a query, and reuse it on forms, reports, and other queries.

I can verify this function works from testing it in the immediate window
?getBillAmount(null,null,null)
0
?getBillAmount(100,3,25)
37.5
?getBillAmount(100,7,25)
50
?getBillAmount(100,5,25)
60

 

[idavis1900]

Jamaarnee

I tried your code but I receive a error (the expression you entered contains invalid syntax. Also the ([billAmount]-nz([Ded],0) apply for whole if statement and with you code it looks like it does not.

Majp,

I would like to use the custom function and it looks like it would work but I dont remember how to do a customer function, it has be a long time since I have build a database

 

[MajP]

1) Paste the function into a standard module.
2) use it in a query, form, or report

=getBillAmount([billAmount],[ExpenseCategoryID],[ded])

 

[TheAceMan1]

How are ya idavis1900 . . .

. . . and this:

[blue]=IIf([ExpenseCategoryID]=7,50.00,IIf([ExpenseCategoryID]=3,0.5*([billAmount]-nz([ded],0)),0.8*([billAmount]-nz([ded],0))))[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[idavis1900]

I'm good TheACEMAN1, how about you. Your code worked but the [billamount]-nz[ded],o also has to be apart of the [expensecategoryID]=7. Becasue if there was a ded the person would not get the $50.00.

MAJP, I now have a module name getBillamount and on my form I have bound text boxes bill amount, ded, expenseCategoryid & percent. I would like the result to show up in the percent box. Where would I put this module, I tried on get foucs of the percent box that did not work.

My coworkers give me their medical bills they have to met a 300.00 deductible, the depending on the service they get a check for either 80% of the bill-ded or 50% or $50.00 they can also get $30.00 depending on the type of bill. So far I have be doing this with a excel file and going back in their folder to make should they have not hand the bill in before. I figure that a database would work better.

thanks Irene

 

[TheAceMan1]

[blue] ... but the [billamount]-nz[ded],o also has to be apart of the [expensecategoryID]=7. [purple]Becasue if there was a ded the person would not get the $50.00.[/purple][/blue]

Herein lies the problem. ded [blue]needs to be included in the comparsion logic![/blue]

This changes the IIF statement to:

[blue]=IIf([purple][b][ExpenseCategoryID]=7 AND nz([ded],0)=0[/b][/purple],50.00,IIf([ExpenseCategoryID]=3,0.5*([billAmount]-nz([ded],0)),0.8*([billAmount]-nz([ded],0))))[/blue]

Give it a shot and let me know.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[idavis1900]

Majp,

I'm still tried to figure out how to use the public function you told me about.

I have a query using my tblBill. I have billamount, ded, & expensecatID field on the query. The I put the public function (getbillamount in a blank field. When I try running the query I get a error code undefined fuction getbillamount in expression. Can you tell me what I'm doing wrong.

 

[MajP]

if you have a form or report with these fields
billAmount
ExpenseCategoryID
ded

Then you can use the function in a calculated control:

=getBillAmount([billAmount],[expenseCatID],[ded])

The function should be in a standard module and public (not the form or report module). Make sure all the spelling is correct.

 

[idavis1900]

I'm sorry that I'm having a hard time understanding this, it problem is very simple.

I can verify the function in the immediate window there I can see that it works.

On my form I have
billAmount
ExpenseCategoryID
ded

I add a unbound text box and in the control source I put

=getBillAmount([billAmount],[expenseCatID],[ded])

and the in view mode I get #name?

Did I put the code in the wrong spot

 

[idavis1900]

Does it matter that ExpenseCatID is a Combo Box

 

[idavis1900]

Ok, I got it to work in my form thank you, but can you give me some more help. So far I have in the Code three select case 3, 7 & else. What if I need to had a case 4 that would be $30. Plus case 7 that is $50.00 it puts $50.00 which is only true if they met thier full ded

getbillamount(100,7,25)

they should only get $25.00 or

if getbillamount(100,7,100)

they would not get anything because they did not met the did

any idea on how to change the code.

Thanks

 

[MajP]

I think you mean

getbillamount(100,7,25)
they should only get 50.00 (not $25.00). If you meant 25.00 please explain.

Public Function getBillAmount(billAmount As Variant, ExpCat As Variant, ded As Variant) As Currency
  Dim multiple As Single
  billAmount = Nz(billAmount, 0)
  ded = Nz(ded, 0)
  ExpCat = Nz(ExpCat, 0)

  Select Case ExpCat
    Case 3
     multiple = 0.5
    Case 4
      getBillAmount = 30
      Exit Function
    Case 7
      If billAmount > ded Then
         getBillAmount = 50
      End If
      Exit Function
    Case Else
      multiple = 0.8
  End Select
 
  getBillAmount = (billAmount - ded) * multiple
End Function

Test Cases:
?getbillamount(100,4,25)
30
?getbillamount(100,7,25)
50
?getbillamount(100,7,100)
0

 

[idavis1900]

Thank you for taken the time to help me. This form is to deal with co-workers medical bills. They have a $300.00 ded they need to met of a year per. So say that they have met $275.00 of their ded for the year. Then hand in a bill for $100.00, $25.00 would go to the $300.00(yearly ded) & they would get the remained $25.00.

I hope I explain that ok

 

[MajP]

A little confused on your cases

  Select Case ExpCat
    Case 3
     multiple = 0.5
    Case 4
      getBillAmount = 30
      Exit Function
    Case 7
      If ded = 0 Then
         getBillAmount = 50
      ElseIf ded <= 50 Then
        getBillAmount = 50 - ded
      End If
      Exit Function
    Case Else
      multiple = 0.8
  End Select

?getbillamount(100,7,25)
25

?getbillamount(100,7,100)
0
?getbillamount(100,7,200)
0