Overflow Error

Question

I have a very simple query that pulls in a single field from another query.  The field contains only the words "Yes" or "No."  With no filter on that field, the new query runs fine, but when I enter "Yes" in the critieria line (since I want to filter for yes records), I get an "Overflow" error.  Since there is only one field and the query runs fine when there is nothing in the criteria line, this has to be the issue.  But I sure can't figure out why the error is caused.  Ideas?

Thanks!

Answer 1

In the root query the field in question is a calculated field:

     TwentyorMore: IIf([3rdYear]>19,"Yes","No")

The one-field query (filters for Yes) based on the root query worked earlier in the week and long before this week as well, which makes this so puzzling.

I would next look at the data type of the [3rdYear] field, and look for any unusual values in that field; especially any that would have been entered in the table this week.

If you can't spot where the problem likes, you might try (in VBA code) opening a recordset on the root query, and looping through the records.  For each record, assign the value of the calculated [TwentyorMore] field to a variable.  If and when an error is raised, enter debug mode and examine the contents of the current record to see if you can tell what the problem is.

Answer 2

I have a very simple query that pulls in a single field from another query.  The field contains only the words "Yes" or "No."  With no filter on that field, the new query runs fine, but when I enter "Yes" in the critieria line (since I want to filter for yes records), I get an "Overflow" error.  Since there is only one field and the query runs fine when there is nothing in the criteria line, this has to be the issue.  But I sure can't figure out why the error is caused.  Ideas?

This post should probably be designated as a question, not a discussion.

What is the exact SQL of the query, and what is the data type (in the table) of the field that you are querying and filtering?

Thx, yes, it is a question.  I was unable to move it, however.

In the root query the field in question is a calculated field:

     TwentyorMore: IIf([3rdYear]>19,"Yes","No")

The one-field query (filters for Yes) based on the root query worked earlier in the week and long before this week as well, which makes this so puzzling.

One thing I did notice . . . when I click on the filter at the top of the field in the root query with the calculated Yes/No entries and attempt to filter for either "Yes" or "No," the same Overflow error occurs.  That must mean the error originates in the root query and not in the one-field query that first stumped me.  One other puzzler; the root query has 4 fields with similar formulas calcuating a Yes/No result, and there is no trouble filtering for either response in the other three.

Answer 3

I have a very simple query that pulls in a single field from another query.  The field contains only the words "Yes" or "No."  With no filter on that field, the new query runs fine, but when I enter "Yes" in the critieria line (since I want to filter for yes records), I get an "Overflow" error.  Since there is only one field and the query runs fine when there is nothing in the criteria line, this has to be the issue.  But I sure can't figure out why the error is caused.  Ideas?

This post should probably be designated as a question, not a discussion.

What is the exact SQL of the query, and what is the data type (in the table) of the field that you are querying and filtering?