Formula in Access Query 3

[AGoodGuy]

I have a query in Access 2007 and I have this below:

=IF([Master Renewal Analysis].[Latest Renewal Notice Premium])>([Master Renewal Analysis].[Original Written Premium]),"Uprate", IF(([Master Renewal Analysis].[Latest Renewal Notice Premium])<])>([Master Renewal Analysis].[Original Written Premium]),"Downrate","No Change"))

I have this in a formula inmy Excel spreadsheet; how to write this in a query in Access and get the same results as in my spreadsheet that will show which items are Uprate, Downrate or No Change

Please keep in mind that in Excel ([Master Renewal Analysis].[Latest Renewal Notice Premium]) is in Column D and ])>([Master Renewal Analysis].[Original Written Premium]) is Column C

 

[MCuthill]

AGoodGuy,

Is what you have shown above the syntax in MS Access or MS Excel?

Also, I believe you have a typo (outlined in red):

=IF([Master Renewal Analysis].[Latest Renewal Notice Premium])>([Master Renewal Analysis].[Original Written Premium]),"Uprate", IF(([Master Renewal Analysis].[Latest Renewal Notice Premium])[COLOR=red]<])>[/color]([Master Renewal Analysis].[Original Written Premium]),"Downrate","No Change"))

IF Statement in Access work exactly the same as Excel, except the Syntax is IIF(Condition,IfTrue,IfFalse)

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]

I think I did have a typo error. So by using IFF statement below I will get the same results in Access??

=IFF([Master Renewal Analysis].[Latest Renewal Notice Premium])>([Master Renewal Analysis].[Original Written Premium]),"Uprate", IFF(([Master Renewal Analysis].[Latest Renewal Notice Premium])<([Master Renewal Analysis].[Original Written Premium]),"Downrate","No Change"))

 

[kjv1611]

I've not looked at it in depth, but yes, using the IIF statement in Access queries is very kin to using IF() in Excel. It's of course not the same syntax, but should give the same results..

 

[AGoodGuy]

I have tried this in my query in Access:

=IFF([Master Renewal Analysis].[Latest Renewal Notice Premium])>([Master Renewal Analysis].[Original Written Premium]),"Uprate", IFF([Master Renewal Analysis].[Latest Renewal Notice Premium])<([Master Renewal Analysis].[Original Written Premium]),"Downrate","No Change")

It is not working for me. It gives me an error that says the expression contains invalid syntax or I need to enclose the text data in quotes. Your thoughts??

 

[MCuthill]

AGoodGuy,

At a glance, I'd say bracketting is the issue. You seem to enclose fields in "(" ")" -- I'm not sure this is necessary. If it is needed, you have mismatched when you have used them.

Please try the following in place of what you have provided:

=IFF([Master Renewal Analysis].[Latest Renewal Notice Premium]>[Master Renewal Analysis].[Original Written Premium],"Uprate", IFF([Master Renewal Analysis].[Latest Renewal Notice Premium]<[Master Renewal Analysis].[Original Written Premium],"Downrate","No Change"))

Hope this helps!

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 tried your suggestion and now it says Undefined function IFF in expression.

 

[dhookom]

In Access, there is no IFF() function. There is an IIf().

Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

That did it. Thanks Dhookum, Mcuthill and kvj1611.

 

[MCuthill]

oops!
My apologies AGoodGuy... I really should have opened MS Access to check that.

Thanks Duane!

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 have another question for you. In my query in Access I have a field called Policy Number which is listed the policy number as: ACA1234567. The letter "A" stands for Access which is the name of the company. "CA" represents the state California. I have the following states with the same setup: AGA (Georgia) AFL (Florida). My question is in my Access query how would I formulate to show the actual name of the state. For example if I take the first 3 characters ACA to show California; how would I formulate that?

 

[dhookom]

Do you have a table of states or companystates? If not, create one and store either AGA or GA with the state name. Then you can create a state column in your query with an expression like:

State: Mid([PolicyNumber],2,2)

Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

Thanks dhookum...that did it. By chance is there a way to show the whole state name? Just asking.....

 

[dhookom]

You didn't answer my question

Do you have a table of states or companystates?

Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

No I did not have a table of the states. I guess I could create a table of the states and bounce that off my query to show the states full name.

 

[AGoodGuy]

I have a query in Access 2007 and I am trying to write a formula but need a little help:

Change: IIf([dbo_renrec.ren_amt AS [Renewal Premium Offer]>[02ppols renewal].prem AS [Original Written Premium],"Uprate",IIf([dbo_renrec.ren_amt AS [Renewal Premium Offer]>[02ppols renewal].prem AS [Original Written Premium],"Downrate","No Change"))

I am not sure what the problem is...your thought??

 

[dhookom]

It would help if you provided an explanation of your logic/requirements rather than just an expression that doesn't work.

There is no "AS" in IIf()s. Also, I think one > should be <. Try:

Change: IIf([dbo_renrec.ren_amt > [02ppols renewal].prem, "Uprate",IIf([dbo_renrec.ren_amt<[02ppols renewal].prem,"Downrate","No Change"))

Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

Dhookem...you are correct...It was a typo on my end. I thought since I changed the expression name in the query that I had to include it in the formula. I believe this formula will work for me. Thanks as always.

 

[AGoodGuy]

Question for you... I have a table and a query that I created below:

STATE POLICY FEE Table (Table I created from scratch)
State
Transaction
State policy fees
Term
Payplan



03Renewal Offere Analysis (Query)
STATE
PolicyNumber
Policy Fee

I am trying to see if there the policy fees from the table and query match and dont match. For example if a policy fee in my table is $50.00 but the policy fee in the query is $30.00 I would need to identify that policy number. How can I determine that in a query?

 

[dhookom]

You can't get this resolved unless the State field in the table is a unique value and it matches the value stored in the STATE column in the query.

When you describe your tables and queries and use the word "match" then the least you should do is tell us which fields would "match", which fields are common, and if one of them is unique.

Duane
Hook'D on Access
MS Access MVP