Use of the between operator

[azimuth79]

Good morning all,


I have what I'm guessing is a pretty simple question, but for whatever reason, I can't get this figured out.


I am creating a report based on a recommendations table I have populated. In this table, there is a Coating field, which is a numeric field consisting of numbers between 0 and about 30. In my report, I need to break this one field down into two separate rows, Coating that is 0 to 10 and coating that is greater than 10.

I thought that I could use an expression in the control source to return a the value if it falls between 0 and 10 for one text box, and then >10 in the other, but I must be using the wrong syntax or something. Here is what my non-programming minded brain tells me

= [Coating] Between 0 and 10 for one box

= [Coating] >10 in the other

That seems logical to me, lol, but I'm sure it isn't correct. If anyone could point me in the right direction, it would be much appreciated.

Cheers to all, and have a wonderful holiday season!!!

J. Miller

 

[rainman89]

can you use an iif statement to do what you want

so for row one you would use
=iif( 0>[coating]<10, coating, "")

row 2 would be
=iif(coating>10, coating, ""

just a guess

 

[Jcase]

For the control source you could use

=iif(0<=Coating<=10, "Between 0-10", "Not Between 0-10")

This would work for both rows, just make sure to change the "Between 0-10" and "Not Between 0-10" with whatever math/expressions you need.
(Remove the = in <= or >= if you don't want the coating values equal to 0 or 10 )