Overflow when add criteria

G

Guest

I have a query that runs fine with a couple of calculations in it. One of
the fields is a simple division to calculate a percentage. As soon as I put
a criteria on the field (in this case <.75), and try to run the query, I get
an overflow error. If I remove the criteria and re-run it, it runs fine.

When it runs, the result for that field and the ones it is using in the
calcualtion, all look normal (no nulls, text, etc).

Any thoughts?

Thanks!
 
A

Allen Browne

Switch the query to SQL View (View menu, in query design), and paste the SQL
statement here. Make sure the "< .75" is in the statement so we can see how
it is being applied.

If this is not a calculated field, open your table in design view, and tell
us the Data Type of the field you are applying this criteria to.

If it is a calculated field, when you run the query (without the .75 so it
runs), does this column align left (like text) or right (like numbers)?
 
G

Guest

Below is the SQL. The field in question is called "variance". When the
query runs, the result of that field aligns to the right.

SELECT [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE, [Jobs Prod
Compl not issued-get compl date].[Prod Compl], Now()-[maxofjldate] AS [Days
since compl], [Jobs Prod Compl not issued-get compl date].Job, [Jobs Prod
Compl not issued-get compl date].Level, dbo_JJMTL.JMPART AS Part,
dbo_JJMTL.JMDESC AS [Desc], dbo_JJMTL.JMEXTD AS [Ext Desc], dbo_JJMTL.JMRCMP
AS [Job Recd Compl], dbo_JJMTL.JMEQTY AS [Est Qty], dbo_JJMTL.JMRQTY AS
[Issued Qty], [jmeqty]-[jmrqty] AS [Under Issued], IIf([jmpart] Like
"PA*",IIf(nz([jmrqty])=0,1,0),1) AS Qualifier,
CDbl(nz([jmrqty],0))/(nz([jmeqty],0)) AS Variance
FROM [Jobs Prod Compl not issued-get compl date] LEFT JOIN dbo_JJMTL ON
([Jobs Prod Compl not issued-get compl date].Level = dbo_JJMTL.JMLEVL) AND
([Jobs Prod Compl not issued-get compl date].Job = dbo_JJMTL.JMJOB)
GROUP BY [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE, [Jobs Prod
Compl not issued-get compl date].[Prod Compl], Now()-[maxofjldate], [Jobs
Prod Compl not issued-get compl date].Job, [Jobs Prod Compl not issued-get
compl date].Level, dbo_JJMTL.JMPART, dbo_JJMTL.JMDESC, dbo_JJMTL.JMEXTD,
dbo_JJMTL.JMRCMP, dbo_JJMTL.JMEQTY, dbo_JJMTL.JMRQTY, [jmeqty]-[jmrqty],
IIf([jmpart] Like "PA*",IIf(nz([jmrqty])=0,1,0),1),
CDbl(nz([jmrqty],0))/(nz([jmeqty],0))
HAVING (((Now()-[maxofjldate])>4) AND (([jmeqty]-[jmrqty])>0) AND
((IIf([jmpart] Like "PA*",IIf(nz([jmrqty])=0,1,0),1))=1) AND
((CDbl(nz([jmrqty],0))/(nz([jmeqty],0)))<0.75))
ORDER BY Now()-[maxofjldate] DESC;

Thanks!
 
A

Allen Browne

If jmeqty is zero or null, you have a division-by-zero error in your
Variance field. That's probably the cause of the Overflow error.

Handle the zero-divisor as a special case, and use Nz() after the division:
CDbl(Nz(IIf([jmeqty]=0, 0, [jmrqty] / [jmeqty]), 0)) AS Variance

Personally, I don't trust Access to get Nz() right without specifying the
2nd argument (as above.)

That applys to the previous field as well, but you can just reverse the
logic so the null is handled by the Else case, i.e. the 3rd argument of
IIf():
IIf([jmpart] Like "PA*", IIf([jmrqty] <> 0, 0,1 ) AS Qualifier

Since these fields are part of the GROUP BY clause, they could be handled in
either the WHERE or HAVING clauses. You might like to see if it is more
efficient if you choose Where instead of Group By on these fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

McGrinch said:
Below is the SQL. The field in question is called "variance". When the
query runs, the result of that field aligns to the right.
Click to expand...
SELECT [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE,
[Jobs Prod Compl not issued-get compl date].[Prod Compl],
Now()-[maxofjldate] AS [Days since compl],
[Jobs Prod Compl not issued-get compl date].Job,
[Jobs Prod Compl not issued-get compl date].Level,
dbo_JJMTL.JMPART AS Part,
dbo_JJMTL.JMDESC AS [Desc],
dbo_JJMTL.JMEXTD AS [Ext Desc],
dbo_JJMTL.JMRCMP AS [Job Recd Compl],
dbo_JJMTL.JMEQTY AS [Est Qty],
dbo_JJMTL.JMRQTY AS [Issued Qty],
[jmeqty]-[jmrqty] AS [Under Issued],
IIf([jmpart] Like "PA*", IIf(nz([jmrqty])=0,1,0), 1) AS Qualifier,
CDbl(nz([jmrqty],0))/(nz([jmeqty],0)) AS Variance

FROM [Jobs Prod Compl not issued-get compl date] LEFT JOIN dbo_JJMTL
ON ([Jobs Prod Compl not issued-get compl date].Level = dbo_JJMTL.JMLEVL)
AND ([Jobs Prod Compl not issued-get compl date].Job = dbo_JJMTL.JMJOB)

GROUP BY [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE,
[Jobs Prod Compl not issued-get compl date].[Prod Compl],
Now()-[maxofjldate],
[Jobs Prod Compl not issued-get compl date].Job,
[Jobs Prod Compl not issued-get compl date].Level,
dbo_JJMTL.JMPART,
dbo_JJMTL.JMDESC,
dbo_JJMTL.JMEXTD,
dbo_JJMTL.JMRCMP,
dbo_JJMTL.JMEQTY,
dbo_JJMTL.JMRQTY,
[jmeqty]-[jmrqty],
IIf([jmpart] Like "PA*", IIf(nz([jmrqty])=0,1,0), 1),
CDbl(nz([jmrqty],0))/(nz([jmeqty],0))

HAVING (((Now()-[maxofjldate])>4)
AND (([jmeqty]-[jmrqty])>0)
AND ((IIf([jmpart] Like "PA*",IIf(nz([jmrqty])=0,1,0),1))=1)
AND ((CDbl(nz([jmrqty],0))/(nz([jmeqty],0)))<0.75))

ORDER BY Now()-[maxofjldate] DESC;


ORDER BY Now()-[maxofjldate] DESC;
 
G

Guest

PERFECT! Thanks for the help!


Allen Browne said:
If jmeqty is zero or null, you have a division-by-zero error in your
Variance field. That's probably the cause of the Overflow error.

Handle the zero-divisor as a special case, and use Nz() after the division:
CDbl(Nz(IIf([jmeqty]=0, 0, [jmrqty] / [jmeqty]), 0)) AS Variance

Personally, I don't trust Access to get Nz() right without specifying the
2nd argument (as above.)

That applys to the previous field as well, but you can just reverse the
logic so the null is handled by the Else case, i.e. the 3rd argument of
IIf():
IIf([jmpart] Like "PA*", IIf([jmrqty] <> 0, 0,1 ) AS Qualifier

Since these fields are part of the GROUP BY clause, they could be handled in
either the WHERE or HAVING clauses. You might like to see if it is more
efficient if you choose Where instead of Group By on these fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

McGrinch said:
Below is the SQL. The field in question is called "variance". When the
query runs, the result of that field aligns to the right.
Click to expand...
SELECT [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE,
[Jobs Prod Compl not issued-get compl date].[Prod Compl],
Now()-[maxofjldate] AS [Days since compl],
[Jobs Prod Compl not issued-get compl date].Job,
[Jobs Prod Compl not issued-get compl date].Level,
dbo_JJMTL.JMPART AS Part,
dbo_JJMTL.JMDESC AS [Desc],
dbo_JJMTL.JMEXTD AS [Ext Desc],
dbo_JJMTL.JMRCMP AS [Job Recd Compl],
dbo_JJMTL.JMEQTY AS [Est Qty],
dbo_JJMTL.JMRQTY AS [Issued Qty],
[jmeqty]-[jmrqty] AS [Under Issued],
IIf([jmpart] Like "PA*", IIf(nz([jmrqty])=0,1,0), 1) AS Qualifier,
CDbl(nz([jmrqty],0))/(nz([jmeqty],0)) AS Variance

FROM [Jobs Prod Compl not issued-get compl date] LEFT JOIN dbo_JJMTL
ON ([Jobs Prod Compl not issued-get compl date].Level = dbo_JJMTL.JMLEVL)
AND ([Jobs Prod Compl not issued-get compl date].Job = dbo_JJMTL.JMJOB)

GROUP BY [Jobs Prod Compl not issued-get compl date].MaxOfJLDATE,
[Jobs Prod Compl not issued-get compl date].[Prod Compl],
Now()-[maxofjldate],
[Jobs Prod Compl not issued-get compl date].Job,
[Jobs Prod Compl not issued-get compl date].Level,
dbo_JJMTL.JMPART,
dbo_JJMTL.JMDESC,
dbo_JJMTL.JMEXTD,
dbo_JJMTL.JMRCMP,
dbo_JJMTL.JMEQTY,
dbo_JJMTL.JMRQTY,
[jmeqty]-[jmrqty],
IIf([jmpart] Like "PA*", IIf(nz([jmrqty])=0,1,0), 1),
CDbl(nz([jmrqty],0))/(nz([jmeqty],0))

HAVING (((Now()-[maxofjldate])>4)
AND (([jmeqty]-[jmrqty])>0)
AND ((IIf([jmpart] Like "PA*",IIf(nz([jmrqty])=0,1,0),1))=1)
AND ((CDbl(nz([jmrqty],0))/(nz([jmeqty],0)))<0.75))

ORDER BY Now()-[maxofjldate] DESC;


ORDER BY Now()-[maxofjldate] DESC;
Thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

overflow error 5
Overflow error on calculate field in query - Help Please 1
Numeric Field Overflow 1
Access Dcount (multiple criteria) 3
Numeric Field Overflow Error 5
Numeric Field Overflow when appending date - how to convert it to text 1
Overflow 1
Overflow message 1

Top