Query with formula's HELP< HELP >HELP

[MTHarris]

K, everyone willing to read this. I have had a long term project that I
work on from time to time. It is a database for testing materials. In my
sole query that I am making that has to have the things on it that are on
it, I have alot of formula's (adding, subtracting, multiplying, etc) the
thing is I got to within two formula's being put in and it started poping up
query too complex. I only two to go for it "would have been complete- for
many many moons". Anyone know anything that I might do to get around this
or deal with it- other than putting lighter fluid on the laptop and setting
it on fire? lol

P.S. this query is basically a the structure for a analysis form..
approximately 11 weight (grams) input points, first formulas totals all
weights, next eleven formulas independently devide individual weight input
by total.. multiply by 100 to get to percent. Next formula totals all
percent to self check - to make sure adds to 100%. Last eleven formula's
subtract each individual preceding percents from 100.. which derives percent
passing. Basically gives percent for size based on weight.

Any help would be tremendously appreciated.

MTHarris

 

[John Vinson]

K, everyone willing to read this. I have had a long term project that I
work on from time to time. It is a database for testing materials. In my
sole query that I am making that has to have the things on it that are on
it, I have alot of formula's (adding, subtracting, multiplying, etc) the
thing is I got to within two formula's being put in and it started poping up
query too complex. I only two to go for it "would have been complete- for
many many moons". Anyone know anything that I might do to get around this
or deal with it- other than putting lighter fluid on the laptop and setting
it on fire? lol

Now, now - hardware solutions almost never solve software problems!
<g>

The "Query Too Complex" error arises when the compiled query exceeds
64KBytes. You can save bytes here and there by using the shortest
understandable names for calculated fields (e.g. PctDn rather than
[Percentage of Work Done]); aliasing all the Table names to one-letter
aliases and using them in field references; avoiding ALL unnecessary
expressions; using Switch() instead of nested IIF() calls; and, when
necessary, writing custom VBA functions and calling them instead of
writing out a monstrous long expression. It sounds like you're close
enough that some of the simpler of these techniques should save you.

 

[Ken Snell]

Just to add to John's advice and suggestions, the error that you see also
can result from an error occurring in a function that is being called in the
expressions... for example, if you have a Null value in a field and the
function that you're using on that field can't handle Null values, you can
see this "query too complex" error.

--
Ken Snell
<MS ACCESS MVP>

K, everyone willing to read this. I have had a long term project that I
work on from time to time. It is a database for testing materials. In my
sole query that I am making that has to have the things on it that are on
it, I have alot of formula's (adding, subtracting, multiplying, etc) the
thing is I got to within two formula's being put in and it started poping up
query too complex. I only two to go for it "would have been complete- for
many many moons". Anyone know anything that I might do to get around this
or deal with it- other than putting lighter fluid on the laptop and setting
it on fire? lol

Now, now - hardware solutions almost never solve software problems!
<g>

The "Query Too Complex" error arises when the compiled query exceeds
64KBytes. You can save bytes here and there by using the shortest
understandable names for calculated fields (e.g. PctDn rather than
[Percentage of Work Done]); aliasing all the Table names to one-letter
aliases and using them in field references; avoiding ALL unnecessary
expressions; using Switch() instead of nested IIF() calls; and, when
necessary, writing custom VBA functions and calling them instead of
writing out a monstrous long expression. It sounds like you're close
enough that some of the simpler of these techniques should save you.