Help with mathmatical formula in query

[Bronte1226]

I have an formula that I am trying to convert from Excel to Access. I keep getting formatting error messages. Either that a decimal or parenthesis is out of place or something (text which isn't text) should be in quotes. It is a eye-crossing formula, but I would so appreciate some assistance or feedback on getting this monster to work!

Example Data
ID STATIC FLOW 25 TOTALFLOW
6 30 25 835

I am trying to use the query to update TotalFlow based on this formula

=IF((SQR(([Hyd]![STATIC]-20)/([Hyd]![STATIC]-[Hyd]![FLOW])))* [Hyd]![25]>[Hyd]![25],ROUND(SUM(SQR((A2-20)/(A2-B2))*C2)+(((SQR((A2-20)/(A2-B2))*C2)-C2)*0.1),0.5),ROUND(SUM(SQR(([Hyd]![STATIC] -20)/([Hyd]![STATIC]-[Hyd]![FLOW] ))*[Sheet1]![25] )+(((SQR(([Hyd]![STATIC]-20)/([Hyd]![STATIC]-[Hyd]![FLOW] ))* [Hyd]![25])-[Hyd]![25])*0.1),0.5)),(([Hyd]![STATIC]-20)/([Hyd]![STATIC]-[Hyd]![FLOW]))*[Hyd]![25])- [Hyd]![25])*0.1)0.5),ROUND(SUM(SQR(([Hyd]![STATIC]-20)/( [Hyd]![STATIC]-[Hyd]![FLOW]))*[Hyd]![25])+(((SQR(([Hyd]![STATIC]-20)/([Hyd]![STATIC]-[Hyd]![FLOW]))*[Hyd]![25])- [Hyd]![25])*0.1)0.5))

 

[lespaul]

can you describe the logic in that formula in english?

Leslie

Come join me at New Mexico Linux Fest!

 

[SkipVought]

Hi,

Simplify, simplify, simplify.

Substitute your hard-to-understand references with an easy-to-understand mnemonic.

Then translate your Excel IF control structure to IF THEN ELSE pseudocode.

Then convert that to the Access IIF control structure.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[MazeWorX]

Two things first off

The error is triggered by the comma's you have in the formula

0.5),ROUND(SUM(SQR

2nd thing you will have to verify the closing parentheses after

HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[Bronte1226]

Well, I have still been unable to get this to work. I verified the commas, and the opening and closing parenthesis, but I am getting a syntax error now. I renamed some of the columns for testing and to rule out issues with the naming conventions.

This is the query as it stands now.

UPDATE Hyd SET TotalTest =IF((SQR((Hyd]![STATIC]-20)/( Hyd]![STATIC]- [Hyd]![FLOW])))* [Hyd]![TwentyFive]> [Hyd]![TwentyFive],
ROUND(SUM(SQR((Hyd]![STATIC]-20)/( Hyd]![STATIC]- [Hyd]![FLOW]))* [Hyd]![TwentyFive])+(((SQR((Hyd]![STATIC]-20)/( Hyd]![STATIC]- [Hyd]![FLOW]))* [Hyd]![TwentyFive])- [Hyd]![TwentyFive])*0.1),0.5),
ROUND(SUM(SQR((Hyd]![STATIC]-20)/( Hyd]![STATIC]- [Hyd]![FLOW]))* [Hyd]![TwentyFive])+(((SQR((Hyd]![STATIC]-20)/( Hyd]![STATIC]- [Hyd]![FLOW]))* [Hyd]![TwentyFive])- [Hyd]![TwentyFive])*0.1),0.5));

Could it be that it is just too much in one update for Access to handle? Should it just be broken down into steps?
I have a request in with the person that created the initial query to sit down and go over the logic, but the meeting hasn't occurred yet. It would be nice to get this beast off my plate.

 

[dhookom]

For starters, in Access use IIf() rather than IF().

Anything this complex, I would never try to create in the SQL of a query. Consider creating a small user-defined function. I also would avoid hard-coding the values 20, 0.1, and 0.5 into any expression.

Duane
Hook'D on Access
MS Access MVP

 

[Bronte1226]

What do you suggest instead of hard coding Duane?

 

[dhookom]

I almost always assume the values will change over time. This suggests the values should be either stored in a table or created in a module of business calculations.

My function might look something like:

Public Function GetTotalTest(dblStatic as Double, _
      dblFlow as Double, intTwentyFive as Integer ) as Double
[green]   'these memory variable should have better names and could be
   '   created as global variables so they could be used everywhere
   '   and updated in one place if needed[/green]
   Dim intSubtract as Integer
   Dim dblMultiplier as Double
   Dim dblPoint as Double
   Dim dblReturnValue as Double

   intSubtract = 20
   dblMultiplier = 0.1
   dblPoint = 0.5
[green]   'no numbers below here [/green]
   If SQR(dblStatic-intSubtract)/(dblStatic-dblFlow)...
[green]       'all the lines of code for the calculation   [/green]

   End If
   GetTotalTest = dblReturnValue
End Function

Duane
Hook'D on Access
MS Access MVP

 

[GKChesterton]

My first choice would be nested queries. If Bronte1226 is unused to VBA, there's no need to go there in my humble opinion.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GingerR]

GK-good luck if you're the one taking over Bronte's app!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GKChesterton]

Unfortunately I'm not, but I wish you the best also!