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:

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

[Tim Marshall]

cvollberg via AccessMonster.c om 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