Re: Changing criteria in queries
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Mon, 17 Oct 2005 13:36:50 -0500
Dear Luther:
Was the overflow caused by cases where POSEN1 + POSEN2 was zero? In cases
where this would be to divide by zero, what result did you want from your
query? Since you are filtering for POSEN1 + POSEN2 >= 100000, It would seem
it doesn't matter. So, I suggest you replace:
AND
((([POSEN1]/([POSEN1]+[POSEN2]))*100)<70) AND
((([POSEN2]/([POSEN1]+[POSEN2]))*100)<70) AND
with
AND
(POSEN1 / IIF(POSEN1 + POSEN2) < 100000, 1, POSEN1 + POSEN2) * 100) < 70 AND
(POSEN2 / IIF(POSEN1 + POSEN2) < 100000, 1, POSEN1 + POSEN2) * 100) < 70
AND
This prevents dividing by zero and may eliminate the overflow.
I don't see where changing the datatypes will help with the overflow
problem.
Tom Ellison
"Luther" <Luther@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:686EDB58-CE50-4A89-8C21-7D9AC874E2FD@xxxxxxxxxxxxxxxx
> Tom,
>
> This is going to be a on-time change; the only reason is the dreaded
> "Overflow error" that I get when I ran these queries. Here is an example
> of
> that caused this:
>
> UPDATE HIPlanNames
> SET POS = "84"
> WHERE (POS Is Null AND INDVTOT=True AND
> POSInd=True AND NonHMO_POS=True AND
> UnknownPOS=False AND POSEN1 Is Not Null AND
> POSEN2 Is Not Null AND
> ((([POSEN1]/([POSEN1]+[POSEN2]))*100)<70) AND
> ((([POSEN2]/([POSEN1]+[POSEN2]))*100)<70) AND
> (([POSEN1]+[POSEN2])>=100000));
>
>
> "Tom Ellison" wrote:
>
>> Dear Luther:
>>
>> I'm going to guess that you want to compare the values as numbers. In
>> other
>> works, this makes the string "1000" to be less than "999", whereas
>> comparing
>> them as strings "9" > "1".
>>
>> You could change the queries to CLNG("1000") < CLNG("999") to compare the
>> integer equivalent.
>>
>> All this begs the serious issue of why you are changing data types. Are
>> you
>> going to be changing them back and forth repeatedly, or is this a
>> one-time
>> change? Will the subsequent column eventually contain letters as well as
>> digits? Is "1000" less than or greater than "A10"?
>>
>> Tom Ellison
>>
>>
>> "Luther" <Luther@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:D7600DAE-35EE-4559-B096-6E9CF799F68D@xxxxxxxxxxxxxxxx
>> > Hello,
>> >
>> > Is there a way to change the criteria values in queries when the data
>> > type
>> > chages also? For example, if I change a field data type from number to
>> > text,
>> > I would also like the criteria value to also change from say <1000 to
>> > <"1000". I have a multitude of these and right now, it's being done
>> > manually.
>> >
>> > Any help would be appreciated.
>>
>>
>>
.
- Prev by Date: Re: Crosstab query value
- Next by Date: Re: Running long queries?
- Previous by thread: Re: Crosstab query value
- Next by thread: Re: Running long queries?
- Index(es):
Relevant Pages
|
