IIF statements (1 Viewer)

[Fastone]

I need to do some calculations. These calculations will depend from several things.
Firstly, it depends if the business is new or already exsiting.
New business is business which has been achieved that current month.
Than, it depends on weather or not the broker worked alone or if he/she also used a marketer.

Here is the iif statement:
iif([Individual Policy Holders].[Date of first policy]=Date(), iif([Broker Company/Branch].[Company Number]=Any other Company Number, iff([Broker and Marketer Information].[Function]=Broker,0,15,0,05),0,20), iff([Broker and Marketer Information].[Function]=Broker,0,20,0,03))

I need to know two things:
1: How can the date criteria only be the month and the year and not the day (I have put in Date so far cause that is the only thing i could find).
2: This is probably a little bit more complicated.
If the business is new and the broker is used alone the commission will be 20%, otherwise it is split 15/5. Broker/Marketer info is in one table. Can anybody tell me how I should change the 'Any Other Company Number' to a correct input.

Anybody can think of doing this an easier way maybe?

Thanks

Stijn

P.S.: I was going to input this into the function I have already received.

 

[simongallop]

First an Iif statement is made up of 3 parts. Iif(Bit under test, action if True, action if False) In your example where you have = Broker it is then followed by 4 bits rather than 2. It won't work!

Second date: Many ways of doing this! Here is one: Iif(Format([Individual Policy Holders].[Date of first policy],"mm/yy")=format(Date(),"mm/yy")..... Otherways could use datediff etc. but think this is simpler

Third: If broker is by itself would the number be a whole number? If so then you could either see if the number contains a '/' by using InStr function or test the number to see if it contains characters other than numeric

HTH

PS On the date thing, would suggest that instead of testing it against Date() that you test it against a textbox in a form that has the date. This is so that you can run reports for the whole month on the first day of the next month by typing in a date from the previous month. Also if you need to run historical rep[orts, you can still use the same setup without having to change anything

 

[Fastone]

Thanks, I try that.

The iif statement: Every iif function has only 3 parts.

1. iif([Individual Policy Holders].[Date of first policy]=Date(),

2. iif([Broker Company/Branch].[Company Number]=Any other Company Number,

3. iff([Broker and Marketer Information].[Function]=Broker,15%,5%),20%),

4. iff([Broker and Marketer Information].[Function]=Broker,20%,3%))


iff([Individual Policy Holders].[Date of first policy]=Date(),2 (which includes statement 3), 4)

But if you know any less confusing way of getting to the same result please let me know.

 

[Fastone]

Third: If broker is by itself would the number be a whole number? If so then you could either see if the number contains a '/' by using InStr function or test the number to see if it contains characters other than numeric

I looked at the InStr function but what would I fill in for start, expr1 and expr2 because these are not constant. The use will have to enter the Broker number in a pop-up box and than it will show this.
Also, it shouldn't compare broker number but company/Branch numbers and these consist out of numbers and letters.

 

[simongallop]

My question was to see if the following is true:

Broker: Number (ie25)
Broker / Marketer: Broker Num followed by '/' followed by Marketer Num ie(25/14)

IF that is the case then:

IIf(InStr([Field where above data is stored],"/") >0, "Marketer exists", "No / so therefore only broker exists")

HTH

 

[Fastone]

I will try this (if the module starts working ).
So, you would change the complete iif function (iif([Broker Company/Branch].[Company Number]=Any other Company Number) to what you just gave me?

Just in case this doesn't work, I'll give you the path I followed to get to this function.

I was thinking that if the Company or Branch number occured twice in the Broker Company table that this would mean that two people worked with this company, than looking if it was a broker and marketer or not.

Thanks