Overflow error - change field type in query?

[Lady_Jane]

Hi everyone,


The query that is causing me trouble (posted below) is based on several other queries.

SELECT Region,  MIN(RoadLengthPerM2) as BestRL, MAX(RoadLengthPerM2) as worstRL, MIN(pctAG) as BestPctAG, MAX(pctAG) as WorstPctAG, MIN(pctDEV) as BestPctDEV, MAX(PctDEV) as WorstPctDEV, MIN(PctStreamsAltered) as BestASL, MAX(PctStreamsAltered) as WorstPctASL
FROM qry_RatioAttributes
GROUP BY Region

Running this query gives me an "overflow" error. Apparently this is because I am trying to fit a value into an incorrect field type, but I have no idea what the field type resulting from a query is.

qry_RatioAttributes gets all of the values used in the above query from double fields calculated as percentages of one another, and rounded to 3 decimal places.

Any insight will be appreciated, could it be this rounding of numbers that is causing trouble?

[jzwp11]

I do not see a problem with the query you posted, but out of curiosity, how did you round the values in qry_RatioAttributes?

[Lady_Jane]

Here is an excerpt of qry_RatioAttributes:

...Round((Sum(tbl_Roads.RoadLength)/tbl_CatchmentsCSL.CatchmentArea),8) AS RoadLengthPerM2, Round((qry_LandCoverRaw.AG/tbl_CatchmentsCSL.CatchmentArea),5)*100 AS PctAG...

I'm working on re-creating that query using more sub-queries... we'll see if that helps.

[jzwp11]

I don't see an issue, so I cannot offer any suggestions. Hopefully you will find a solution.

[Lady_Jane]

It turns out that one of my columns from qry_RatioAttributes was returning an #Error. If I tried to sort by that column it would return an overflow error, so naturally it could not find it's min or max values either.

[jzwp11]

Now that you found the issue were you able to solve it? I am thinking that you may need to use the NZ() function or an IIF() function to force a numeric result

[Lady_Jane]

I'm just using a "WHERE field <> "value"" in the original query to remove those records. I think in the long-term I'm gonna have to re-organize the original tables to get rid of this error.