expression is typed incorrectly

[cvollberg via AccessMonster.com]

Good morning, I need a little help, I keep getting this error when I run this
report. I have narrowed it down to the query (sql view) listed below. the
error is: "This expression is typed incorrectly, or it is too complex to be
evaluated"

SELECT ActiveBranch.[Branch Name], -Sum([MthEnd]/[MonthClosingUnit]) AS
[AncillaryFee$MTD], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YTD], -Sum(
[MthEnd]/[MonthClosingVolume])*10000 AS AncillaryFeeBpsMTD, -Sum([YTD]/
[YTDClosingVolume])*10000 AS AncillaryFeeBpsYTD, Sum(-[MthEnd]) AS
TotalExpMthly, Sum(-[YTD]) AS TotalExpYTD
FROM (tblACPLTrialBalanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBalanceHistory.CostCenter = ActiveBranch.CostCenter) INNER JOIN
tblGENRClosingSummary ON ActiveBranch.CostCenter = tblGENRClosingSummary.
CostCenter
WHERE (((tblACPLTrialBalanceHistory.ActgPeriod) Between [Forms]!
[frmACLSRptsMTDYTD]![MthBegDt] And [Forms]![frmACLSRptsMTDYTD]![MthEndDt])
AND ((tblACPLTrialBalanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300902"))
GROUP BY ActiveBranch.[Branch Name];
Any help would be greatly appreciated

Christine

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Tim Marshall]

cvollberg via AccessMonster.com wrote:

Good morning, I need a little help, I keep getting this error when I run this
report. I have narrowed it down to the query (sql view) listed below. the
error is: "This expression is typed incorrectly, or it is too complex to be
evaluated"

SELECT ActiveBranch.[Branch Name], -Sum([MthEnd]/[MonthClosingUnit]) AS
[AncillaryFee$MTD], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YTD], -Sum(

What are you trying to do with " -Sum("? Get a negative value?

Try -1 * sum(whatever) instead. Same thing with the - in front of your
field expressions as well.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[cvollberg via AccessMonster.com]

this calculates fees that are then added to the loan amount. Not sure why
they built it this way I just took it over. It worked fine a week ago and
then we got this error. I will try your solution and let you know. Thanks

Tim Marshall wrote:

Good morning, I need a little help, I keep getting this error when I run this
report. I have narrowed it down to the query (sql view) listed below. the

[quoted text clipped - 3 lines]

SELECT ActiveBranch.[Branch Name], -Sum([MthEnd]/[MonthClosingUnit]) AS
[AncillaryFee$MTD], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YTD], -Sum(

What are you trying to do with " -Sum("? Get a negative value?

Try -1 * sum(whatever) instead. Same thing with the - in front of your
field expressions as well.

--
Message posted via http://www.accessmonster.com

[cvollberg via AccessMonster.com]

Using the -1* did not change the result and we still got the error

cvollberg wrote:

this calculates fees that are then added to the loan amount. Not sure why
they built it this way I just took it over. It worked fine a week ago and
then we got this error. I will try your solution and let you know. Thanks

Good morning, I need a little help, I keep getting this error when I run this
report. I have narrowed it down to the query (sql view) listed below. the

[quoted text clipped - 6 lines]

Try -1 * sum(whatever) instead. Same thing with the - in front of your
field expressions as well.

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

Tim Marshall wrote:

Best way to do this is write
another query with the same from and where clause buy not a select query

I meant to say: "Best way to do this is write
another query with the same from and where clause but not a group by
query", ie, drop the group by clause and don't use sum()

Believe it or not, English *IS* my mother tongue!!!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

cvollberg via AccessMonster.com wrote:

Using the -1* did not change the result and we still got the error

Rats.

Since it was working previously and hasn't been changed (you're
absolutely sure it hasn't changed?), the next thing I'd check very
carefully is to ensure that there is absolutely no way a zero can be in
any of the denominators you're using. Best way to do this is write
another query with the same from and where clause buy not a select query
- this is air sql here, pasted from your original:

SELECT ActiveBranch.[Branch Name], MonthClosingUnit, YTDClosingUnit,
MonthClosingVolume
FROM (tblACPLTrialBalanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBalanceHistory.CostCenter = ActiveBranch.CostCenter) INNER JOIN
tblGENRClosingSummary ON ActiveBranch.CostCenter = tblGENRClosingSummary.
CostCenter
WHERE (((tblACPLTrialBalanceHistory.ActgPeriod) Between [Forms]!
[frmACLSRptsMTDYTD]![MthBegDt] And [Forms]![frmACLSRptsMTDYTD]![MthEndDt])
AND ((tblACPLTrialBalanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300902"))

If there ary zeros in the above, this could be causing it.

Other than those two things, nothing else immediately jumps out at me.
You'd then need to trouble shoot it by examining the query structure.

The approach I'd then take is slowly reconstructing the original query
in several ways. For example, starting with just one of the
fields/calulated fields in the select clause running it and seeing if
it's OK, then trying it with another component of the current select
clause by itself and then combining the tested fields and so on. The
same thing could then be done separately or in conjunction with the
previous sentence with items in the where clause - try it without any
where clause, build up one condition at a time.

I'd probably concentrate on doing the "slow build" approach with the
select clause first

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[cvollberg via AccessMonster.com]

OK, there is 0's in the numbers so that if probably what is causing the error,
isn't there a statement I can put in front of the equations that will allow
them and not cause the error, what is really funny the report comes out right.
Thanks

Tim Marshall wrote:

Using the -1* did not change the result and we still got the error

Rats.

Since it was working previously and hasn't been changed (you're
absolutely sure it hasn't changed?), the next thing I'd check very
carefully is to ensure that there is absolutely no way a zero can be in
any of the denominators you're using. Best way to do this is write
another query with the same from and where clause buy not a select query
- this is air sql here, pasted from your original:

SELECT ActiveBranch.[Branch Name], MonthClosingUnit, YTDClosingUnit,
MonthClosingVolume
FROM (tblACPLTrialBalanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBalanceHistory.CostCenter = ActiveBranch.CostCenter) INNER JOIN
tblGENRClosingSummary ON ActiveBranch.CostCenter = tblGENRClosingSummary.
CostCenter
WHERE (((tblACPLTrialBalanceHistory.ActgPeriod) Between [Forms]!
[frmACLSRptsMTDYTD]![MthBegDt] And [Forms]![frmACLSRptsMTDYTD]![MthEndDt])
AND ((tblACPLTrialBalanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialBalanceHistory.[GLAcct#])="300902"))

If there ary zeros in the above, this could be causing it.

Other than those two things, nothing else immediately jumps out at me.
You'd then need to trouble shoot it by examining the query structure.

The approach I'd then take is slowly reconstructing the original query
in several ways. For example, starting with just one of the
fields/calulated fields in the select clause running it and seeing if
it's OK, then trying it with another component of the current select
clause by itself and then combining the tested fields and so on. The
same thing could then be done separately or in conjunction with the
previous sentence with items in the where clause - try it without any
where clause, build up one condition at a time.

I'd probably concentrate on doing the "slow build" approach with the
select clause first

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[cvollberg via AccessMonster.com]

I have now narrrowed it to the 4 -Sum(.... calculations that are causing the
problem, I take one out get the error, take them all out and it works with no
error. so now how to I put the calculation back in and not get the error?

cvollberg wrote:

OK, there is 0's in the numbers so that if probably what is causing the error,
isn't there a statement I can put in front of the equations that will allow
them and not cause the error, what is really funny the report comes out right.
Thanks

Using the -1* did not change the result and we still got the error

[quoted text clipped - 38 lines]

I'd probably concentrate on doing the "slow build" approach with the
select clause first

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Tim Marshall]

cvollberg via AccessMonster.com wrote:

OK, there is 0's in the numbers so that if probably what is causing the error,
isn't there a statement I can put in front of the equations that will allow them and not cause the error,
You need to decide what information is required if the MonthClosingUnit,
YTDClosingUnit, MonthClosingVolume, or YTDClosingVolume is returned as
zero. Knowing nothing about your organization, from the field names, it
seems to me that your fractions are value/number of items sold. I'll
guess that this is right. If so, that would mean that if the number of
units sold in a time period is zero, you would want zero.

If my guess is on the right track, an iif() function could be used to
check if units sold (MonthClosingUnit) is zero, then use zero,
otherwise, use value/units sold (MthEnd/MonthClosingUnit). Model this
for the other fractions. I'll stay away from the -sum because I'm not
familiar with it and assume you want a negative number:

Instead of:

-Sum([MthEnd]/[MonthClosingUnit]) AS [AncillaryFee$MTD]

Use (air code, but hopefully will give you the right idea):

Sum( -1 * (iif([MonthClosingUnit] = 0, 0, ([MthEnd]/[MonthClosingUnit])
) ) ) AS [AncillaryFee$MTD]
what is really funny the report comes out right.

That tells me that the report recordsource is very likely somehow
different from the query you're using.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[cvollberg via AccessMonster.com]

Your awesome, it worked, thank you so much

Tim Marshall wrote:

OK, there is 0's in the numbers so that if probably what is causing the error,
isn't there a statement I can put in front of the equations that will allow them and not cause the error,

You need to decide what information is required if the MonthClosingUnit,
YTDClosingUnit, MonthClosingVolume, or YTDClosingVolume is returned as
zero. Knowing nothing about your organization, from the field names, it
seems to me that your fractions are value/number of items sold. I'll
guess that this is right. If so, that would mean that if the number of
units sold in a time period is zero, you would want zero.

If my guess is on the right track, an iif() function could be used to
check if units sold (MonthClosingUnit) is zero, then use zero,
otherwise, use value/units sold (MthEnd/MonthClosingUnit). Model this
for the other fractions. I'll stay away from the -sum because I'm not
familiar with it and assume you want a negative number:

Instead of:

-Sum([MthEnd]/[MonthClosingUnit]) AS [AncillaryFee$MTD]

Use (air code, but hopefully will give you the right idea):

Sum( -1 * (iif([MonthClosingUnit] = 0, 0, ([MthEnd]/[MonthClosingUnit])
) ) ) AS [AncillaryFee$MTD]

what is really funny the report comes out right.

That tells me that the report recordsource is very likely somehow
different from the query you're using.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Tim Marshall]

cvollberg via AccessMonster.com wrote:

Your awesome, it worked, thank you so much

Hurray, someone beside my mom thinks so! 8)

Seriously, you should really investigate what the query/sql is behind
the report you mentioned. It does sound as if it's very different and
if the two results (the one via query and the one via report) are
supposed to be the same, then you might have some trouble.

My guess is the recordsource for the report might give individual
details which are added together on report section headers, giveing the
same effect as the original query you posted. The original developer
might have had some version of the iif() in the individual report
controls that do the fractions in the section headers.

That's just a guess, though.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[cvollberg via AccessMonster.com]

Tim,

Ok I have solved almost all of this reports problem but one. I have this one
subreport that is still giving me the error, I think it is because I have one
record in the result set giving me an #error in the VOSBps field and I have
not been able to figure out how to get rid of it.

This is the expression I use: VOSBps: ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000), now it just so happens that the VOS$ and the LoanAmt are 0, is this
creating the error and if so how do I fix it?

any help is greatly appreciated.

Christine

Tim Marshall wrote:

Your awesome, it worked, thank you so much

Hurray, someone beside my mom thinks so! 8)

Seriously, you should really investigate what the query/sql is behind
the report you mentioned. It does sound as if it's very different and
if the two results (the one via query and the one via report) are
supposed to be the same, then you might have some trouble.

My guess is the recordsource for the report might give individual
details which are added together on report section headers, giveing the
same effect as the original query you posted. The original developer
might have had some version of the iif() in the individual report
controls that do the fractions in the section headers.

That's just a guess, though.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[cvollberg via AccessMonster.com]

Forgot to give what the report is doing with it: all it is doing is taking
the totals from the query and doing the following: =Sum([SumOfVOS$])/Sum(
[SumOfLoanAmt])*10000

cvollberg wrote:

Tim,

Ok I have solved almost all of this reports problem but one. I have this one
subreport that is still giving me the error, I think it is because I have one
record in the result set giving me an #error in the VOSBps field and I have
not been able to figure out how to get rid of it.

This is the expression I use: VOSBps: ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000), now it just so happens that the VOS$ and the LoanAmt are 0, is this
creating the error and if so how do I fix it?

any help is greatly appreciated.

Christine

Your awesome, it worked, thank you so much

[quoted text clipped - 12 lines]

That's just a guess, though.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[Tim Marshall]

cvollberg via AccessMonster.com wrote:

This is the expression I use: VOSBps: ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000), now it just so happens that the VOS$ and the LoanAmt are 0, is this
creating the error and if so how do I fix it?

ZeroVal2 must be a function of some sort, probably in a standard module.
Regardless, it's not causing the problem (or all of the problem), it's
the zeros in loanamt. And only when all values for the group value are
zero.

This is totally air code, but try something similar to the iif()
approach you did previously (watch wrap):

VOSBps: iif(Sum([LoanAmt]) = 0, 0, ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000))
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[cvollberg via AccessMonster.com]

Thanks Tim,

worked great, I was trying to use the same idea early just could not get it
to work right. Thanks again.

christine

Tim Marshall wrote:

This is the expression I use: VOSBps: ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000), now it just so happens that the VOS$ and the LoanAmt are 0, is this
creating the error and if so how do I fix it?

ZeroVal2 must be a function of some sort, probably in a standard module.
Regardless, it's not causing the problem (or all of the problem), it's
the zeros in loanamt. And only when all values for the group value are
zero.

This is totally air code, but try something similar to the iif()
approach you did previously (watch wrap):

VOSBps: iif(Sum([LoanAmt]) = 0, 0, ZeroVal2(Sum([VOS$])/Sum([LoanAmt])
*10000))

--
Message posted via http://www.accessmonster.com