BETWEEN . . . AND Operator

J

John Drake

Hi, Please help.

I have a text box in a form and in the CONTROL SOURCE of this text box I
want to use and IFF fuction that evaluates a number.

If the number is between 200 and 250, then it must print "FINE", if not it
should print "CRITICAL".
How will it work if I have more than 5 such BETWEEN ... AND statements for
the same field.

The field name in which this number is located is called Results.

Thanks
John
 
A

Allen Browne

John, create a table where you can enter the value and matching text.

This is much easier to maintain. You can tweak the values or add ranges just
by updating the table, and everywhere in the software behaves consistently.

Tom Ellision explains how to set up such a table here:
http://allenbrowne.com/ser-58.html
 
B

bhicks11 via AccessMonster.com

Hi John,

I have nested up to 4 with no problem.

If(this,"Fine",if(that,"somethingelse",if(theother,"somethingother) etc.

So you nest the next condition in the false part of the if function.

Bonnie
http://www.dataplus-svc.com
 
J

John Drake

Hi, thanks for the reply.

The problem is that I have to do this for the
value <200, "something1", or if the
value is between 200 and 250, "something2", or if the
value is between 260 and 350, "something3", or if the
value between 360 and 450, "something4", or if the
value is between 460 and 600, "something5", or if the
value is between 610 and 910, "something6", or if the
value is >910, "something7".

To compound the problem even further is that I am a NEWBIE - sorry.
Thanks
 
J

John Drake

Hi,

The this is that I already have the field in one of my tables, along with
many others.
 
A

Allen Browne

You have what field in your table?
The one that contains the numeric value such as 200?

That's fine: what I'm suggesting is another table to hold the names of the
ranges.
 
B

bhicks11 via AccessMonster.com

Same deal John,

If(me.field<200,dothis,if(me.field>199 and me.field<251,dothis,if(me.
field>259 and me.field<351, dothis, etc.

Make sure you have enough close parens at the end.

Or use VBA (make a GotFocus event) that does something like this (just
grabbed an example that has no relevance to your stuff):

If isNull(Me.City) Then
If (Not IsNull(varState)) Then Me![STATE] = varState
If (Not IsNull(varCity)) Then Me![CITY] = varCity
If (Not IsNull(varState)) Then Me![STATE].SetFocus
End If

Or use CASE statement.


John said:
Hi, thanks for the reply.

The problem is that I have to do this for the
value <200, "something1", or if the
value is between 200 and 250, "something2", or if the
value is between 260 and 350, "something3", or if the
value between 360 and 450, "something4", or if the
value is between 460 and 600, "something5", or if the
value is between 610 and 910, "something6", or if the
value is >910, "something7".

To compound the problem even further is that I am a NEWBIE - sorry.
Thanks
Hi John,
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
Top