how do i query in the form?

[linger]

HI

i have 3 input fields (R, B and L) in my form. i need to find the min and max values of the 3 fields. i've heard about using the crosstab query but i'm not sure how to do it.

R B L Min Max
3 5 6 3 6
6 2 3 3 6

Much help will be greatly appreciated. I feel like banging my head on the wall right now.

yiling

 

[SydneyGeek]

Here are the columns you mentioned...

R B L Min Max 
3 5 6 3 6 
6 2 3 3 6

In the Min field create this expression:

MinValue:Iif([R]<= and [R]<=[L],[R],Iif(<=[R] and <=[L],,[L]))

In the Max field, create this expression:

MaxValue:Iif([R]>= and [R]>=[L],[R],Iif(>=[R] and >=[L],,[L]))

HTH

Denis

 

[linger]

ok..

i see the logic... so if i had 5 input fields, to compare, i should use if-then-else statements? if so, where should i put the expression? in the query or in the form itself? i am a little confused. sorry.

thanks

 

[SydneyGeek]

The Iif statements will do the job, but to do 5 fields you will need to nest 4 of them. The easiest place to do it is in the query on which the form is based, that way if you need to use the same calculation elsewhere you don't have to do it all over again.
Open the query in Design view. If you don't already have Min and Max fields, go to the first blank column and put in the MinValue expression that I gave you. In the adjacent column put the MaxValue expression. Those calculated fields can now be used on the form.

HTH
Denis

 

[linger]

i am experiencing a problem putting the whole expression into the field. is there a limit to the length of the expression? the expression keeps getting cut off.

the expression i used was to compare 5 inputs. i used 4 nested Iif. like this
MaxThickness:
Iif([CThickness]>=[TThickness] And [CThickness]>=[RThickness] And [CThickness]>=[BThickness] And [CThickness]>=[LThickness], [CThickness], Iif([TThickness]>=[CThickness] And [TThickness]>=[RThickness] And [TThickness]>=[BThickness] And [TThickness]>=[LThickness], [TThickness], Iif([RThickness]>=[CThickness] And [RThickness]>=[TThickness] And [RThickness]>=[BThickness] And [RThickness]>=[LThickness], [RThickness], Iif([BThickness]>=[CThickness] And [BThickness]>=[TThickness] And [BThickness]>=[RThickness] And [BThickness]>=[LThickness], [BThickness], [LThickness]))))

i know it's a little messy but the general logic is the same as yours. so don't get thrown by the vars. I was wondering if it is easier and even possible if I "transposed" the records and just use the built in min max functions in the query.

yiling

 

[linger]

ohh...yeaaaaaaaahhhhhhh....

THanks for your help...I solved it without using a query. I just put the expression straight into the control source of the properties box in the form. Hmmm...I wonder if there would be any implications of doing this in the long run. Do you know of any? If not thanks so much for you time!

YiLing