Solved Help with Calculation Field in Query (1 Viewer)

[Nicole-B]

Hi guys, I am a total beginner here, basically I am having to edit a database built by a co-worker years ago and I am stuggling with the SQL side of things.

One of my fields on my query needs to contain a calculation based on what is present in a couple of other fields and percentage amounts. If i write what i am trying to do could someone please help me figure out where i am going wrong?

EstimatedGrant£: IIf([AnticipatedGrant%]=100,[QuoteCost]-[TitleSearchFee]),IIf([AnticipatedGrant%]=80,([QuoteCost]/100*20)-[TitleSearchFee])

Basically I want the calculation to show the EstimatedGrant£ as either 80% or 100% of the Quote costs minus the TitleSearchFee depending on percentage entered in the AnticipatedGrant% Field)

I hope that all makes sense, I'm hoping its me being stupid and adding a bracket or comma in the wrong place lol

 

[theDBguy]

Hi. Welcome to AWF!

What is the data type of the AnticipatedGrant% column? Maybe you could try it this way too.

EstimatedGrant: [QuoteCost]*[AnticipatedGrant]-[TitleSearchFee]

Just a thought...

 

[Minty]

I'm not sure your calculation is correct but as you only have the two options then

EstimatedGrant£ = IIf([AnticipatedGrant%]=100,[QuoteCost]-[TitleSearchFee], [QuoteCost]/100*20)-[TitleSearchFee])

The syntax is IIf( EvaluatedCondition, TrueResult, FalseResult)

So if it's 100 then return [QuoteCost]-[TitleSearchFee] else return [QuoteCost]/100*20)-[TitleSearchFee])

 

[plog]

Basically I want the calculation to show the EstimatedGrant£ as either 80% or 100% of the Quote costs minus the TitleSearchFee depending on percentage entered in the AnticipatedGrant% Field)
...
EstimatedGrant£: IIf([AnticipatedGrant%]=100,[QuoteCost]-[TitleSearchFee]),IIf([AnticipatedGrant%]=80,([QuoteCost]/100*20)-[TitleSearchFee])

First, don't use special characters (£, %, or spaces) in field names. Makes coding and querying harder.

Second, explain what "going wrong" means. Unexpected results? No results? Error message? Give us a hint.

Third, your logic isn't air tight. If [AnticipatedGrant%] does not equal 100 nor 80 it will return Null. Is that what you want? Are other values of [AnticipatedGrant%] even possible?

Fourth, here's some wild guesses:

A. One of the fields in your calculation is calculated itself in the same query. That means its value isn't available for use in other calculated fields.

B. You forgot about mathematical order of operation: powers, parenthises, multiplication, division, addition, subtraction. [QuoteCost]/100*20 does not yield 80% of [QuoteCost], it yields 20% of [QuoteCost] because [QuoteCost] first gets multiplied by 20 and then divided by 100. Either uses parenthesis or only multiple by .8.

 

[Nicole-B]

Hi Guys, Thanks for your help, I have got there now.

EstimatedGrant£: [QuoteCost]/100*[AnticipatedGrant%]-[TitleSearchFee]

Basically i was trying to get the estimated grant awarded in £ based on an 80% or 100% entitlement (yes i realise my math was wrong in the first one... its been a long day!

Realised i was trying to do it the hard way and instead of asking it to do 2 different calculations i should reword it to make it only one calculation based on the information.

Sorry i didnt put enough info on my request, its the first time I have posted, but i have to admit its a great forum to find answers!

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.