Access Query

[AGoodGuy]

I want to give more details..this is what I have

Added Policy Fee:IIF([DanPols Renewal].STATE]=”AAL”, IIf(Mid([dbo_ppols]![policyno],4,2)="00","New Business","Renewal") IIF [DanPols Renewal].Term ="1" or "3" or "6" or "12" then Add the [ppols renewal].fee

Basically If in the state of Alabama (ALA) and if the (Term) is 6 months and the description is "renewal" then add the policy fee to it.

 

[dhookom]

Are you going to write this type of expression for every different set of values?

IMO, you should have data in tables that identify all of your business rules.

At a minimum, I would create a small user-defined function that accepts some arguments and spits back the desired value.

Duane
Hook'D on Access
MS Access MVP

 

[MCuthill]

AGoodGuy,

then add the policy fee to it

Add the policy fee to what?

Are you meaning, populate the renewal fee into this field under the criteria given?

If you are wanting to "add" it to this record, and get the group total (by state for instance) - you will need a query to populate the fee conditionally, then a query referencing this one to "Group By" and Sum.

Cheers! Hope this helped!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AGoodGuy]

I am so sorry that I did not make myself clear. This is what I have. I have a query and in this query I have the field names State, policy fee and term. For the field name (State)I have it listed at the bottom. I also have the field name (policy fee), (Term) and (Desc) which are the term of the policy,the policy fee and the description of the fee. What I am trying to do is query an expression that will give me the added fee amount if the State is ALA and the term is 6 or 12 months with a NB or Renewal description then add the policy fee and the state fee to give me the added fee amount.

Example

STATE Policy Fee Desc Term State Policy Fee Added Fee Amount
ALA $4.00 NB 6 $3.00 ???
ALA $50.00 Renewal 6 $3.00
ALA $63.00 NB 12 $3.00
ALA $76.00 Renewal 12 $3.00

I hopes this makes more sense.

 

[MCuthill]

AGoodGuy,

The following should be a good starting point for you:

Added Fee Amount: IIF(([Field1]=[Criteria1] AND [Field2]=[Criteria2] AND ... ), [Policy Fee]+[State Policy Fee],0)

This will return Policy Fee plus the State Policy Fee where all the criteria are met, and zero when they are not all met.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AGoodGuy]

MCuthill:

So if I have the following formula below:

Amount Fee Added: IIF (([State]=AAL AND [Desc]=New Business and [Term] = 6, [Policy Fee]+ [State Fee],0

That will give me the Added Fee Amount?

 

[AGoodGuy]

MCuthill:

So if I have the following formula below:Amount Fee Added: IIF (([State]=AAL AND [Desc]=New Business and [Term] = 6, [Policy Fee]+ [State Fee],0

That will give me the Added Fee Amount?


Keep in mind that I already have in my query the description to give mw whether a policy is New Business or Renewal

Description: IIf(Mid([dbo_ppols]![policyno],4,2)="00","New Business","Renewal")

 

[dhookom]

Your "Example" lacks structure. Please learn how to use TGML to format the posting so we don't have to guess where one column starts and another begins. This is particularly true when you have spaces in field names. The very minimum requirement is to type []s around field names containing spaces.

Where does the 3 come from?

As I suggested previously, I think you need to create a small function like:

Function GetAddedFee(strState as String, strPolicyNo as String, _
        strTerm as String, dblStateFee as Double) As Double
  If strState = "ALA" And Mid(strPolicyNo,4,2) = "00" AND strTerm IN ("6", "12") Then
    GetAddedFee = dblStateFee
   Else
    GetAddedFee = 0
  End If
End Function

From your initial post, you are suggesting the Term field is a string and not numeric.


Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

The Term Field is numeric. Is the above code you sent is used as an expression in a query?

 

[AGoodGuy]

This is what I am trying to accomplish:

STATE prem fee Description Term State Fee AAL $470.00 $2.00 New Business 12 $3.00
AAL $652.00 $2.00 Renewal 06 $9.00
AAL $664.00 $2.00 New Business 03 $7.00
AAL $636.00 $2.00 Renewal 01 $6.00

I am trying to write an exrpession in my query that if the state is AAL and the Description is New Business and the term is 12 (Numeric) then the State fee is given; I want to have calculated the Added Fee Amount by adding the State Fee and the Prem.

What you suggested; can that be used in as an expression in a query or are you suggesting I used that formula in the report in Access?

 

[SkipVought]

The Term Field is numeric

Then why...

IIF [DanPols Renewal].[b][highlight]Term ="1" or "3" or "6" or "12"[/highlight][/b]

so it should be...

IIF [DanPols Renewal].Term =1  or [DanPols Renewal].Term =3 or [DanPols Renewal].Term =6 or [DanPols Renewal].Term =12

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Please try to be more accurate and responsive. Can you see how

Term ="1" or "3" or "6" or "12"

can be misleading?

Can you also respond to "Where does the 3 come from?"

A user function can be used be used almost anywhere a standard function can be used. When I copied the function, I missed the word "Public " to the left of "Function". You would create a new module and paste the function into it. Then save the module with the name "modBusinessCalcs".

Then create a column in your query using the function and use your column/field names as the arguments.

I'm not sure the function will work as I wrote it because I still don't understand your logic or where the values come from.
Since Term in numeric, you might want to change the function to:

Pulbic Function GetAddedFee(strState as String, strPolicyNo as String, _
        dblTerm as Double, dblStateFee as Double) As Double
  If strState = "ALA" And Mid(strPolicyNo,4,2) = "00" AND dblTerm IN (6, 12) Then
    GetAddedFee = dblStateFee
   Else
    GetAddedFee = 0
  End If
End Function

Duane
Hook'D on Access
MS Access MVP