negative number between values returns incorrect result

[gmazza via AccessMonster.com]

Hey there,
I have a form that allows the user to enter a number in a field.
In the after update of the field, I have the following code:
Dim answer As Double
Dim answer1 As Double

answer = DLookup("Min", "CriteriaOption", "ClinicalTrialId = " &
GetActiveStudy() & " and SequenceNo = 2 ")
answer1 = DLookup("Max", "CriteriaOption", "ClinicalTrialId = " &
GetActiveStudy() & " and SequenceNo = 2 ")

If text1 >= answer And text1 <= answer1 Then
Else
MsgBox ("You must enter a number between " & answer & " and " & answer1)
text1 = ""
End If

After debugging and making sure everything was grabbing from the right place,
it was determined that Min was -2.3333 and Max was -9.7777.
When I enter a -6, it returns the MsgBox, when -6 is clearly between my min
and max.

Any ideas what I must be doing wrong?

My Min and Max datatypes are:
Field Size: Decimal
Format: General Number
Precision: 18
Scale: 4
Decimal Places: 4

 

[KARL DEWEY]

The math is wrong. -2.3333 is larger than -9.7777.
-6 < -2.3333 and -6 > -9.7777
text1 >= answer And text1 <= answer1

 

[John Spencer]

Well,
-6 is less than -2.333
-6 is greater than -9.7777

If you remember the number line, values to the left of a number are smaller
then the number amd conversely values to the right of a number are larger than
the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

And, adding to the confusion, also note that JET-BETWEEN re-arranges the
arguments so that


? eval("-6 BETWEEN -2 AND -9")
-1


? eval(" -6 >= -2 AND -6 <= -9")
0



where the last expression uses AND rather than BETWEEN.



Vanderghast, Access MVP

 

[gmazza via AccessMonster.com]

So then how do I write my statement to work for both positive and negative
numbers?
Thanks for both your help!

Well,
-6 is less than -2.333
-6 is greater than -9.7777

If you remember the number line, values to the left of a number are smaller
then the number amd conversely values to the right of a number are larger than
the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hey there,
I have a form that allows the user to enter a number in a field.

[quoted text clipped - 26 lines]

Scale: 4
Decimal Places: 4

 

[vanderghast]

Many solutions. Use ABS as appropriate:

if( ABS(rst!field) >= 2.333 AND ABS(rst!field) <= 9.7777 )
Then ...

where the limits are also in absolute value. That assumes you want to test
the intensity, not the sign.

Can also use eval and Between


if ( eval( rst!field.Value & " BETWEEN -2.33333 AND -9.77777
" ) ) Then ...

as example.



Vanderghast, Access MVP

So then how do I write my statement to work for both positive and negative
numbers?
Thanks for both your help!

Well,
-6 is less than -2.333
-6 is greater than -9.7777

If you remember the number line, values to the left of a number are
smaller
then the number amd conversely values to the right of a number are larger
than
the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hey there,
I have a form that allows the user to enter a number in a field.

[quoted text clipped - 26 lines]

Scale: 4
Decimal Places: 4

 

[KARL DEWEY]

Make sure the numbers recorded in Min and Max are correct.
Using -2.3333 as Min and -9.7777 as Max is wrong.
--
Build a little, test a little.

So then how do I write my statement to work for both positive and negative
numbers?
Thanks for both your help!

Well,
-6 is less than -2.333
-6 is greater than -9.7777

If you remember the number line, values to the left of a number are smaller
then the number amd conversely values to the right of a number are larger than
the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hey there,
I have a form that allows the user to enter a number in a field.

[quoted text clipped - 26 lines]

Scale: 4
Decimal Places: 4

 

[Krzysztof Naworyta]

gmazza via AccessMonster.com wrote:
| So then how do I write my statement to work for both positive and
| negative numbers?
| Thanks for both your help!

Dim xTmp

xMin = ...
xMax = ...

If xMin > xMax Then
xTmp = xMin
xMin = xMax
xMax = xTmp
End If

if y >=xMin And y <=xMax Then
.....

 

[John Spencer]

You can compare the Min and Max values and flip them if needed.

Dim v as variant

If Answer > Answer1 then
v = Answer
Answer = Answer1
Answer1 = v
End If

Then you can use your current comparison statements. Or you can use the EVAL
function as noted elsewhere to return true or false.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County