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]/[MonthClosingUni t]) AS
[AncillaryFee$MT D], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YT D], -Sum(
[MthEnd]/[MonthClosingVol ume])*10000 AS AncillaryFeeBps MTD, -Sum([YTD]/
[YTDClosingVolum e])*10000 AS AncillaryFeeBps YTD, Sum(-[MthEnd]) AS
TotalExpMthly, Sum(-[YTD]) AS TotalExpYTD
FROM (tblACPLTrialBa lanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBal anceHistory.Cos tCenter = ActiveBranch.Co stCenter) INNER JOIN
tblGENRClosingS ummary ON ActiveBranch.Co stCenter = tblGENRClosingS ummary.
CostCenter
WHERE (((tblACPLTrial BalanceHistory. ActgPeriod) Between [Forms]!
[frmACLSRptsMTDY TD]![MthBegDt] And [Forms]![frmACLSRptsMTDY TD]![MthEndDt])
AND ((tblACPLTrialB alanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300902"))
GROUP BY ActiveBranch.[Branch Name];
Any help would be greatly appreciated

Christine

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

[Tim Marshall]

cvollberg via AccessMonster.c om 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]/[MonthClosingUni t]) AS
[AncillaryFee$MT D], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YT D], -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]/[MonthClosingUni t]) AS
[AncillaryFee$MT D], -Sum([YTD]/[YTDClosingUnit]) AS [AncillaryFee$YT D], -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.c om 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], MonthClosingUni t, YTDClosingUnit,
MonthClosingVol ume
FROM (tblACPLTrialBa lanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBal anceHistory.Cos tCenter = ActiveBranch.Co stCenter) INNER JOIN
tblGENRClosingS ummary ON ActiveBranch.Co stCenter = tblGENRClosingS ummary.
CostCenter
WHERE (((tblACPLTrial BalanceHistory. ActgPeriod) Between [Forms]!
[frmACLSRptsMTDY TD]![MthBegDt] And [Forms]![frmACLSRptsMTDY TD]![MthEndDt])
AND ((tblACPLTrialB alanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialBa lanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialBa lanceHistory.[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], MonthClosingUni t, YTDClosingUnit,
MonthClosingVo lume
FROM (tblACPLTrialBa lanceHistory INNER JOIN ActiveBranch ON
tblACPLTrialBa lanceHistory.Co stCenter = ActiveBranch.Co stCenter) INNER JOIN
tblGENRClosing Summary ON ActiveBranch.Co stCenter = tblGENRClosingS ummary.
CostCenter
WHERE (((tblACPLTrial BalanceHistory. ActgPeriod) Between [Forms]!
[frmACLSRptsMTDY TD]![MthBegDt] And [Forms]![frmACLSRptsMTDY TD]![MthEndDt])
AND ((tblACPLTrialB alanceHistory.[GLAcct#])="300610" Or
(tblACPLTrialB alanceHistory.[GLAcct#])="300611" Or
(tblACPLTrialB alanceHistory.[GLAcct#])="300802" Or
(tblACPLTrialB alanceHistory.[GLAcct#])="300899" Or
(tblACPLTrialB alanceHistory.[GLAcct#])="300999" Or
(tblACPLTrialB alanceHistory.[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.c om
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.c om
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Tim Marshall]

cvollberg via AccessMonster.c om 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 MonthClosingUni t,
YTDClosingUnit, MonthClosingVol ume, or YTDClosingVolum e 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 (MonthClosingUn it) is zero, then use zero,
otherwise, use value/units sold (MthEnd/MonthClosingUni t). 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]/[MonthClosingUni t]) AS [AncillaryFee$MT D]

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

Sum( -1 * (iif([MonthClosingUni t] = 0, 0, ([MthEnd]/[MonthClosingUni t])
) ) ) AS [AncillaryFee$MT D]
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 MonthClosingUni t,
YTDClosingUnit, MonthClosingVol ume, or YTDClosingVolum e 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 (MonthClosingUn it) is zero, then use zero,
otherwise, use value/units sold (MthEnd/MonthClosingUni t). 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]/[MonthClosingUni t]) AS [AncillaryFee$MT D]

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

Sum( -1 * (iif([MonthClosingUni t] = 0, 0, ([MthEnd]/[MonthClosingUni t])
) ) ) AS [AncillaryFee$MT D]

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.c om
http://www.accessmonster.com/Uwe/For...ccess/200604/1