Changing criteria in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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));
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top