Crazy Calculation continues to get Syntax Error (1 Viewer)

[Heatshiver]

I was given an Excel Workbook where I needed to breakdown some calculations for an Access database. Normally they would start simply enough, but once broken down into their basic inputs, they would be huge. I would then place them in a query and use them as such. However, I am stuck on one that is boggling my mind.

Originally, when I would place the calculation into the query, I would get the error that the text was too long:

(((SUM(((([Empty Cell + Sample (g)]-[Empty Cell (g)])/(50-([Empty Cell + Sample + Water (g)]-[Empty Cell + Sample (g)])))*((([Feed ppg]-[Centrate ppg])/((([Empty Cell + Sample (g)]-[Empty Cell (g)])/(50-([Empty Cell + Sample + Water (g)]-[Empty Cell + Sample (g)])))-[Centrate ppg]))*[Feed gpm])*60)/(([Empty Cell + Sample (g)]-[Empty Cell (g)])/(50-([Empty Cell + Sample + Water (g)]-[Empty Cell + Sample (g)])))/42*(SUM((([Weight of Cyl + SF + Water (g)]-[Empty Cylinder (g)])[Total Wt of Water (g)])/[SG Base Fluid])/(50-([Empty Cell + Sample + Water (g)]-[Empty Cell + Sample (g)]))*100)/100))/([Vol % Oil]/100))*[Daily Hrs Run])

So I decided to take parts of the calculation that were used throughout the equation and put them as separate calculations. I would then integrate those calculations back into the main equation:

(((SUM(((([Expr1])/(50-([Expr2])))*((([Expr3])/((([Expr1])/(50-([Expr2])))-[Centrate ppg]))*[Feed gpm])*60)/(([Expr1])/(50-([Expr2])))/42*(SUM((([Expr4])[Total Wt of Water (g)])/[SG Base Fluid])/(50-([Expr2]))*100)/100))/([Vol % Oil]/100))*[Daily Hrs Run])

Finally, I ensured that the correct tables and fields were being used from the database:

(((SUM(((([Expr1])/(50-([Expr2])))*((([Expr3])/((([Expr1])/(50-([Expr2])))-[tblSolidCtrlSum]![Cent Mud Wgt1]))*[tblSolidCtrlSum]![Feed Mud Wgt1])*60)/(([Expr1])/(50-([Expr2])))/42*(SUM((([Expr4])[tblSolidCtrlSum]![Total Wgt of Water (g) (CRI1)])/[tblFluidSum]![SG Base Fluid])/(50-([Expr2]))*100)/100))/([tblFluidSum]![Vol % Oil]/100))*[Daily Hrs Run])

But this gives me an invalid syntax error (an operand without an operator). I counted out the parentheses several times, and there are 28 for open and close. I even tried taking out a pair of parentheses for each "Expr#" since they would technically no longer be needed, but I get the same issue. I was thinking even the SUM could be an issue, but not sure how...

This is driving me nuts and I've already spent hours creating and trying to fix it. Can anyone please help me?? Thank you.

 

[boblarson]

My suggestion is to get rid of all of this and create a series of functions to do the calculations.

Also, you can't use aliases from calculations in other calculations within the same query. In order to use the aliases you need to use them in ANOTHER query, not the same one.

 

[Heatshiver]

Thanks for the quick response.

So in essence, I can't create smaller calculations and use them in one large calculation within the same query? How would I go about using them if they were in a separate query? Would I need to use VBA?

Thanks again!

 

[boblarson]

What I'm saying is for example:

If you are trying to get

Select Nz([Field1],0) + Nz([Field2],0) - Nz([Field3]),0) As MyCalc, [Field4], [Field5], Nz([Field6],0)/[Field4] As OtherCalc
From Table1

I would save that as a query (all names are just simplified examples):
MyQuery

So then I can use

Select MyCalc * (OtherCalc + MyCalc)) As ThirdCalc, [Field4], [Field5] * MyCalc As FoCalc
From MyQuery

As very simple examples.

 

[Heatshiver]

Sorry, I'm a little slow right now.

So I create a query (MyQuery) with the calculations I would need for a larger calculation. Then in my main query I can reference MyQuery and use them.

I understand MyCalc's calculation, but I'm not understanding OtherCalc:

[Field4], [Field5], Nz([Field6],0)/[Field4] As OtherCalc

What do Field 4 & 5 do, and how do the commas put them together? I keep thinking of them as an if statement: if field 4, then field 5, otherwise Nz([Field6]...

I know this is not the point, but I am very curious now. Thank you for the example.

 

[boblarson]

In my example, fields 4 and 5 are just showing something that isn't having calculations applied. I was trying to show how to include fields that weren't being used in calculations also, for use in the next query.