Conditional query?

[AMT]

Hi! I am trying to write a conditional query using the expression builder. If CONC is greater than MDL and less than RL, I want to put TR in the PARVQ field. I have tried to write the conditional query:
<[RESULTS]![RL] And >[RESULTS]![MDL]

Unfortunately it works for most values, but not all. For example, if MDL is 1 and RL is 20, a value of 6 gets TR. But a value of 31 also gets TR. I don't understand why. Please help!

Thanks.

 

[mstorer]

This may help. It may not be cleanest way, but at least it should work. I'll make the assumption that your CONC field is also in your RESULTS table. In the field, type the following:

PARVQ: IIf([RESULTS]![CONC]<[RESULTS]![RL] And [RESULTS]![CONC]>[RESULTS]![MDL], "TRUE", "FALSE")

I hope this helps.

 

[cbrou]

i hard coded the query to be >1 AND <20 with no errors. Is it possible that your [results]![rl] or [results]![mdl] are returning more than one value?

 

[AMT]

Thanks! I will try the code.

The RL and the MDL have different values for each sample. The CONC field is different for each sample too. So I can't hard code in the values, sad to say.

This may illustrate what I'm trying for:
SAMPLE PARVQ CONC MDL RL
1 TR 6 1 10
2 TR 3.5 1 5
3 = 31 2 20

Any further ideas would be most welcome.

 

[David R]

Is it possible your parameters are reading as text? "between" does have a value in text fields too, an alphabetical meaning.

I believe if you use [Parameter]*1 you'll force it to convert. Messy, but it might work.

HTH,
David R

 

[AMT]

You're right--all the fields are text. Coded that way. I'll try the [Parameter]*1 thing.