Getting error: "The expression you entered is too complex" in quer

[Guest]

Morning all,

In a query, I am creating a field with an expression. Here is what I have:

Current Variance:
IIf(Month([FiscalMonth])=1,[BudgetJan]-[ActualJan],(IIf(Month([FiscalMonth])=2,[BudgetFeb]-[ActualFeb],(IIf(Month([FiscalMonth])=3,[BudgetMar]-[ActualMar],IIf(Month([FiscalMonth])=4,[BudgetApr]-[ActualApr],(IIf(Month([FiscalMonth])=5,[BudgetMay]-[ActualMay],IIf(Month([FiscalMonth])=6,[BudgetJun]-[ActualJun],(IIf(Month([FiscalMonth])=7,[BudgetJul]-[ActualJul],(IIf(Month([FiscalMonth])=8,[BudgetAug]-[ActualAug],(IIf(Month([FiscalMonth])=9,[BudgetSep]-[ActualSep],(IIf(Month([FiscalMonth])=10,[BudgetOct]-[ActualOct])))))))))))))))))

I have been doing this in pieces and running each time for each month to
make sure it works. The above code works fine....UNTIL I add November's line
(or any additional line) and I get "The expression you entered is too
complex." What can I do to solve this?

Thank you,
MN

 

[Guest]

I will add that parentheses....but the code up until November still works
fine. I want to keep in in the query like this....any other reasons why this
would happen?

Within my table...I have fields that are called "Actual Jan", "Budget Jan"
etc etc....I only have the calculations within that field created called
"CurrentVariance"

Thanks,
MN

The following line is missing the leading (

IIf(Month([FiscalMonth])=6

Also if you have fields in your tables like so: [BudgetJan]-[ActualJan], you
have a serious normalization problem. If nothing else, how do you handle
multiple years?

If nothing else, the IIf statement might be better written as a Case
statement in code.


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Morning all,

In a query, I am creating a field with an expression. Here is what I have:

Current Variance:
IIf(Month([FiscalMonth])=1,[BudgetJan]-[ActualJan],(IIf(Month([FiscalMonth])=2,[BudgetFeb]-[ActualFeb],(IIf(Month([FiscalMonth])=3,[BudgetMar]-[ActualMar],IIf(Month([FiscalMonth])=4,[BudgetApr]-[ActualApr],(IIf(Month([FiscalMonth])=5,[BudgetMay]-[ActualMay],IIf(Month([FiscalMonth])=6,[BudgetJun]-[ActualJun],(IIf(Month([FiscalMonth])=7,[BudgetJul]-[ActualJul],(IIf(Month([FiscalMonth])=8,[BudgetAug]-[ActualAug],(IIf(Month([FiscalMonth])=9,[BudgetSep]-[ActualSep],(IIf(Month([FiscalMonth])=10,[BudgetOct]-[ActualOct])))))))))))))))))

I have been doing this in pieces and running each time for each month to
make sure it works. The above code works fine....UNTIL I add November's line
(or any additional line) and I get "The expression you entered is too
complex." What can I do to solve this?

Thank you,
MN

 

[Guest]

Jerry,

Thanks for your help. I actually figured out another way within the query!

Thank you for helping!
MN

The following line is missing the leading (

IIf(Month([FiscalMonth])=6

Also if you have fields in your tables like so: [BudgetJan]-[ActualJan], you
have a serious normalization problem. If nothing else, how do you handle
multiple years?

If nothing else, the IIf statement might be better written as a Case
statement in code.


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Morning all,

In a query, I am creating a field with an expression. Here is what I have:

Current Variance:
IIf(Month([FiscalMonth])=1,[BudgetJan]-[ActualJan],(IIf(Month([FiscalMonth])=2,[BudgetFeb]-[ActualFeb],(IIf(Month([FiscalMonth])=3,[BudgetMar]-[ActualMar],IIf(Month([FiscalMonth])=4,[BudgetApr]-[ActualApr],(IIf(Month([FiscalMonth])=5,[BudgetMay]-[ActualMay],IIf(Month([FiscalMonth])=6,[BudgetJun]-[ActualJun],(IIf(Month([FiscalMonth])=7,[BudgetJul]-[ActualJul],(IIf(Month([FiscalMonth])=8,[BudgetAug]-[ActualAug],(IIf(Month([FiscalMonth])=9,[BudgetSep]-[ActualSep],(IIf(Month([FiscalMonth])=10,[BudgetOct]-[ActualOct])))))))))))))))))

I have been doing this in pieces and running each time for each month to
make sure it works. The above code works fine....UNTIL I add November's line
(or any additional line) and I get "The expression you entered is too
complex." What can I do to solve this?

Thank you,
MN

 

[Guest]

Here is the solution to your problem:

Public Function Calc_Variance(MoNo, bJan, bFeb, bMar, bApr, bMay, bJun,
bJul, bAug, bSep, bOct, _
bNov, bDec, aJan, aFeb, aMar, aApr, aMay, aJun, aJul, aAug, aSep, aOct,
aNov, aDec)
'MoNo is the month to calculate the variance

Case 1
Calc_Variance = bJan - aJan
Case 2
Calc_Variance = bFeb - aFeb
Case 3
Calc_Variance = bMar - aMar
Case 4
Calc_Variance = bApr - aApr
Case 5
Calc_Variance = bMay - aMay
Case 6
Calc_Variance = bJun - aJun
Case 7
Calc_Variance = bJul - aJul
Case 8
Calc_Variance = bAug - aAug
Case 9
Calc_Variance = bSep - aSep
Case 10
Calc_Variance = bOct - aOct
Case 11
Calc_Variance = bNov - aNov
Case 12
Calc_Variance = bDec - aDec
End Select
End Function

Put the above function in a standard module. Then use it like this instead
you what you are doing:
Current Variance:
Calc_Variance([FiscalMonth],[BudgetJan],[BudgetFeb],[BudgetMar],[BudgetApr],[BudgetMay],[BudgetJun],[BudgetJul],[BudgetAug],[BudgetSep],[BudgetOct],[BudgetNov],[BudgetDec]],[ActualJan],[ActualFeb],[ActualMar],[ActualApr],[ActualMay],[ActualJun],[ActualJul],[ActualAug],[ActualSep],[ActualOct],[ActualNov],[ActualDec])

 

[Guest]

The following line is missing the leading (

IIf(Month([FiscalMonth])=6

Also if you have fields in your tables like so: [BudgetJan]-[ActualJan], you
have a serious normalization problem. If nothing else, how do you handle
multiple years?

If nothing else, the IIf statement might be better written as a Case
statement in code.

 

[John Vinson]

Morning all,

In a query, I am creating a field with an expression. Here is what I have:

Current Variance:
IIf(Month([FiscalMonth])=1,[BudgetJan]-[ActualJan],(IIf(Month([FiscalMonth])=2,[BudgetFeb]-[ActualFeb],(IIf(Month([FiscalMonth])=3,[BudgetMar]-[ActualMar],IIf(Month([FiscalMonth])=4,[BudgetApr]-[ActualApr],(IIf(Month([FiscalMonth])=5,[BudgetMay]-[ActualMay],IIf(Month([FiscalMonth])=6,[BudgetJun]-[ActualJun],(IIf(Month([FiscalMonth])=7,[BudgetJul]-[ActualJul],(IIf(Month([FiscalMonth])=8,[BudgetAug]-[ActualAug],(IIf(Month([FiscalMonth])=9,[BudgetSep]-[ActualSep],(IIf(Month([FiscalMonth])=10,[BudgetOct]-[ActualOct])))))))))))))))))

I have been doing this in pieces and running each time for each month to
make sure it works. The above code works fine....UNTIL I add November's line
(or any additional line) and I get "The expression you entered is too
complex." What can I do to solve this?

In addition to the other suggestions, you might try the Choose()
function instead of multiple nested IIF's:

Current Variance: Choose([FiscalMonth], [BudgetJan] - [ActualJan],
[BudgetFeb] - [ActualFeb], <etc>)

The Choose function takes an integer as its first argument and then
returns the first, second, third... remaining argument for values of
1, 2, 3...

John W. Vinson[MVP]

 

[Guest]

Excellent suggestion, John. For a one up, it is better than mine. What I
submitted was cannibalized from a function I use for a similar situation.
The applicaton I currently support is a financial app. We have multiple
queries, reports, and forms that need to present year to date data. Rather
than rewrite the calculation for each situation, being extremely lazy, I put
it in a function I can call from anywhere. To make coding faster, the call
to the function is in the function as a comment line. All I have to do is
copy and paste. Also, I know the results will be consistent.

But, I still like your solution better.

Morning all,

In a query, I am creating a field with an expression. Here is what I have:

Current Variance:
IIf(Month([FiscalMonth])=1,[BudgetJan]-[ActualJan],(IIf(Month([FiscalMonth])=2,[BudgetFeb]-[ActualFeb],(IIf(Month([FiscalMonth])=3,[BudgetMar]-[ActualMar],IIf(Month([FiscalMonth])=4,[BudgetApr]-[ActualApr],(IIf(Month([FiscalMonth])=5,[BudgetMay]-[ActualMay],IIf(Month([FiscalMonth])=6,[BudgetJun]-[ActualJun],(IIf(Month([FiscalMonth])=7,[BudgetJul]-[ActualJul],(IIf(Month([FiscalMonth])=8,[BudgetAug]-[ActualAug],(IIf(Month([FiscalMonth])=9,[BudgetSep]-[ActualSep],(IIf(Month([FiscalMonth])=10,[BudgetOct]-[ActualOct])))))))))))))))))

I have been doing this in pieces and running each time for each month to
make sure it works. The above code works fine....UNTIL I add November's line
(or any additional line) and I get "The expression you entered is too
complex." What can I do to solve this?

In addition to the other suggestions, you might try the Choose()
function instead of multiple nested IIF's:

Current Variance: Choose([FiscalMonth], [BudgetJan] - [ActualJan],
[BudgetFeb] - [ActualFeb], <etc>)

The Choose function takes an integer as its first argument and then
returns the first, second, third... remaining argument for values of
1, 2, 3...

John W. Vinson[MVP]