Overflow message Why am I getting it?

[Lori Hansen via AccessMonster.com]

I have run much larger queries than the one below. Is it because some of the projects have had nothing disbursed and there for its messing up calculating percent disbursed? And if so how do I fix it. The code I am getting overflow on is below.

Thanks!

SELECT DISTINCT OtherCascadeFeeTypes.ProjectServiceYear, OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount, Sum(NZ([Invoice Amount],"0")) AS [Total Disbursed], Sum(NZ(([Invoice Amount]/[PreApprovedAmount]),"0")) AS [Percentage Disbursed], OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm
FROM OtherCascadeFeeTypes LEFT JOIN [Other: Invoices/eService] ON OtherCascadeFeeTypes.ProjectNames = [Other: Invoices/eService].[Project Number]
WHERE ((([Other: Invoices/eService].[Effective Dates]) Between #1/1/2004# And #10/31/2004#))
GROUP BY OtherCascadeFeeTypes.ProjectServiceYear, OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount, OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm
HAVING (((OtherCascadeFeeTypes.ProjectServiceYear) Like "2004") AND ((OtherCascadeFeeTypes.ProjectNames) Not Like "N/A"))
ORDER BY OtherCascadeFeeTypes.PreApprovalDate;

 

[Brian]

I have run much larger queries than the one below. Is it because some of

the projects have had nothing disbursed and there for its messing up
calculating percent disbursed? And if so how do I fix it. The code I am
getting overflow on is below.

Thanks!

SELECT DISTINCT OtherCascadeFeeTypes.ProjectServiceYear,

OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount,
Sum(NZ([Invoice Amount],"0")) AS [Total Disbursed], Sum(NZ(([Invoice
Amount]/[PreApprovedAmount]),"0")) AS [Percentage Disbursed],
OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm

FROM OtherCascadeFeeTypes LEFT JOIN [Other: Invoices/eService] ON

OtherCascadeFeeTypes.ProjectNames = [Other: Invoices/eService].[Project
Number]

WHERE ((([Other: Invoices/eService].[Effective Dates]) Between #1/1/2004# And #10/31/2004#))
GROUP BY OtherCascadeFeeTypes.ProjectServiceYear,

OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount,
OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm

HAVING (((OtherCascadeFeeTypes.ProjectServiceYear) Like "2004") AND

((OtherCascadeFeeTypes.ProjectNames) Not Like "N/A"))

ORDER BY OtherCascadeFeeTypes.PreApprovalDate;

I am deeply suspicious of this:

Sum(NZ([Invoice Amount],"0"))

How can you sum a string? Surely it should be:

Sum(NZ([Invoice Amount],0))

 

[Douglas J. Steele]

In addition to Brian's suggestion, what's the database type of the various
fields you're summing? If, for example, they're Integers but the sum is
greater than 32,767 (the maximum value of an Integer), you'll get an
overflow message. You should be able to get around that by explicitly
converting the data type: Sum(CLng(NZ([Invoice Amount],0))) rather than
Sum(NZ([Invoice Amount],0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have run much larger queries than the one below. Is it because some of

the projects have had nothing disbursed and there for its messing up
calculating percent disbursed? And if so how do I fix it. The code I am
getting overflow on is below.

Thanks!

SELECT DISTINCT OtherCascadeFeeTypes.ProjectServiceYear,

OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount,
Sum(NZ([Invoice Amount],"0")) AS [Total Disbursed], Sum(NZ(([Invoice
Amount]/[PreApprovedAmount]),"0")) AS [Percentage Disbursed],
OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm

FROM OtherCascadeFeeTypes LEFT JOIN [Other: Invoices/eService] ON

OtherCascadeFeeTypes.ProjectNames = [Other: Invoices/eService].[Project
Number]

WHERE ((([Other: Invoices/eService].[Effective Dates]) Between #1/1/2004# And #10/31/2004#))
GROUP BY OtherCascadeFeeTypes.ProjectServiceYear,

OtherCascadeFeeTypes.ProjectNames, OtherCascadeFeeTypes.PreApprovedAmount,
OtherCascadeFeeTypes.PreApprovalDate, OtherCascadeFeeTypes.Firm

HAVING (((OtherCascadeFeeTypes.ProjectServiceYear) Like "2004") AND

((OtherCascadeFeeTypes.ProjectNames) Not Like "N/A"))

 

[George Nicholson]

In addition to Brian and Douglas' remarks: While dividing by zero *usually*
generates division by zero messages, I have also seen it generate overflow
messages occasionally.

If you have trouble finding the cause of the error: make a copy of your
query, delete one field, run it, repeat. When you stop getting the error,
you can zero in on the last field you deleted.